Link between GL and SLA

Link between GL and SLA


GL_JE_BATCHES (je_batch_id)                                   => GL_JE_HEADERS (je_batch_id)
GL_JE_HEADERS (je_header_id)                                  => GL_JE_LINES (je_header_id)
GL_JE_LINES (je_header_id,  je_line_num)                      => GL_IMPORT_REFERENCES (je_header_id, je_line_num)
GL_IMPORT_REFERENCES (gl_sl_link_table, gl_sl_link_id)        => XLA_AE_LINES (gl_sl_link_table, gl_sl_link_id)
XLA_AE_LINES (application_id, ae_header_id)                   => XLA_AE_HEADERS (application_id, ae_header_id)
XLA_AE_HEADERS (application_id, event_id)                     => XLA_EVENTS (application_id, event_id)  
XLA_EVENTS (application_id, entity_id)                        => XLA.XLA_TRANSACTION_ENTITIES (application_id, entity_id) 

Link from AR to SLA to GL

Link from AR to SLA to GL
-- Table Link from AR to SLA to GL in Oracle Apps R12
SELECT *
FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = ''-- Customer_trx_id

SELECT *
FROM HZ_CUST_ACCOUNTS_ALL
WHERE CUST_ACCOUNT_ID = ''--(BILL_TO_CUSTOMER_ID.RA_CUSTOMER_TRX_ALL)

SELECT *
FROM HZ_PARTIES
WHERE PARTY_ID = ''--(PARTY_ID.HZ_CUST_ACCOUNTS_ALL)

SELECT *
FROM XLA_TRANSACTION_ENTITIES
WHERE SOURCE_ID_INT_1 = ''--(CUSTOMER_TRX_ID.RA_CUSTOMER_TRX_ALL)

SELECT *
FROM XLA_AE_HEADERS
WHERE ENTITY_ID = '' -- Entity_Id from XLA_TRANSACTION_ENTITIES

SELECT *
FROM XLA_AE_LINES
WHERE AE_HEADER_ID = '' -- AE_HEADER_ID FROM XLA_AE_HEADERS

SELECT *
FROM GL_IMPORT_REFERENCES
WHERE GL_SL_LINK_ID = ''--GL_SL_LINK_ID FROM XLA_AE_LINES
AND GL_SL_LINK_TABLE = '' --GL_SL_LINK_TABLE FROM XLA_AE_LINES

SELECT *
FROM GL_JE_LINES
WHERE JE_HEADER_ID = ''--JE_HEADER_ID FROM GL_IMPORT_REFERENCES
AND JE_LINE_NUM = '' --LINE NUMBER FROM GL_JE_LINES

SELECT *
FROM GL_JE_HEADERS
WHERE JE_HEADER_ID = '' --JE_HEADER_ID FROM GL_IMPORT_REFERENCES

SELECT *
FROM GL_JE_BATCHES
WHERE JE_BATCH_ID = '' -- JE_BATCH_ID.GL_JE_HEADERS

Link from AP to SLA to GL

Link from AP to SLA to GL
SELECT INVOICE_ID
FROM AP_INVOICES_ALL

SELECT *
FROM XLA.XLA_TRANSACTION_ENTITIES
WHERE SOURCE_ID_INT_1 = 10000 -- INVOICE_ID.AP_INVOICES_ALL

SELECT *
FROM XLA_EVENTS
WHERE EVENT_ID = 37207 -- EVENT_ID.XLA_TRANSACTION_ENTITIES

SELECT *
FROM XLA_AE_HEADERS
WHERE EVENT_ID = 37207 -- EVENT_ID.XLA_TRANSACTION_ENTITIES

SELECT GL_SL_LINK_ID
FROM XLA_AE_LINES
WHERE AE_HEADER_ID = 28257 --AE_HEADER_ID.XLA_AE_HEADERS

SELECT *
FROM GL_IMPORT_REFERENCES
WHERE GL_SL_LINK_ID = 44986 -- GL_SL_LINK_ID.XLA_AE_LINES

SELECT *
FROM GL_JE_HEADERS
WHERE JE_HEADER_ID = 1137482 -- JE_HEADER_ID.GL_IMPORT_REFERENCES

SELECT *
FROM GL_JE_LINES
WHERE JE_HEADER_ID = 1137482 -- JE_HEADER_ID.GL_IMPORT_REFERENCES

SELECT *
FROM GL_JE_BATCHES
WHERE JE_BATCH_ID = 869749 --JE_BATCH_ID.GL_IMPORT_REFERENCES  

List of Operating Units and List of INV Organizations

List of Operating Units

SELECT a.name,
       a.organization_id,
       a.creation_date,
       a.last_update_date
  FROM hr_organization_units a, hr_organization_information b
 WHERE     a.organization_id = b.organization_id
       AND org_information1 = 'OPERATING_UNIT'
       AND org_information2 = 'Y'
     
List of INV Organizations

SELECT a.name,
       a.organization_id,
       a.creation_date,
       a.last_update_date
  FROM hr_organization_units a, hr_organization_information b
 WHERE     a.organization_id = b.organization_id
       AND org_information1 = 'INV'
       AND org_information2 = 'Y'

Xml Publisher Basics

Xml Publisher Basics

1) How wil u set sl.no in xml reports?
  we wil use postion attribute here
 syntax: <?position()?>
2) How wil u give pagebreaks after specific records(rows)?
 we wil use <?split-by-page-break?>
 syntax: <?if:position() mod 5=0 ?><?split-by-page-break:?><?end if?>
    ------  we should not use split-by -page-break in table.
3)  How 2 Avoid the page break inbetween the table
           Navg: In the table properties we wil select row tab
   in that we we wil select check box Allow row 2 break across the page
4)  How wil give Headers to all pages?
 by using start body and end body
 syntax: <?start-body?>
  <?end body?>
5)  Xml publisher can have Only one top level elements
6)  How 2 give count of elemnts in last page of the report?
 syntax: <?start@last-page:body?>
               count attrbute1
               <?end body?>
7)  How to appy colours alternatively?
 syntax: <?if@row:position() mod 2=0?>  <xsl:attribute name="background-color"  xdofo:ctx="incontext">silver</xsl:attribute><?end if?>
8)   How wil  u set Heading per each page?
           Navg: In the table properties we wil select row tab ->
  in that we we wil select check box repeat header for each page
9) Navigation for landscape
           Navg:  pagelayout ->page setup ->landscape(to the text----only 4 tat page)
10) How 2 apply colour in Background
           Navg: borders and shading -> shading -> fill
11) How 2 remove Boarders
           Navg: borders and shading -> borders -> (remove all sides except down)
12) Check box (check box) <?ename>kasi?>
14) Drop dowlist  select drop dronlist give  list of elements then go for add help text
15. If we want to hilight one cell then we wil write syntax like
  <?if:debit>1000?><xsl:
  attribute xdofo:ctx=
  "block" name="backgroundcolor">
  red</xsl:
  attribute><?end if?>
16).we wil  do running totals like this
 <?xdoxslt:set_variable($_
 XDOCTX, ’RTotalVar’, xdoxslt:
 get_variable($_XDOCTX,
 ’RTotalVar’) + INVAMT)?>
17) if i want to calclulate totals --------  <?add-page-total:variablename;'column_name'?>
       to display those columns               <?show-page-total:ct;’$#,##0.00’;’ ($#,##0.00)’?>
18.we can do sorting like this
syn:-<?sort:element name?>
ex:--<?sort:vendor_name?><?sort:vendor_num?>
18. set autotrace traceonly -to trace the query without data
19.select to_char(to_date('10/25/2008','MM/DD/YYYY'),'dd-mon-yyyy') from dual
20. we can execute pl/sql  proceures with EXEC or CALL command;

21) to remove extra 0's in xml
<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"><?CP_COST?></fo:bidi-override>
22) sum of sal
<?sum(SAL[text()])?>       

Query to find procesing time of concurrent program

Query to find the concurrent program processing time:

SELECT f.request_id,
         pt.user_concurrent_program_name user_concurrent_program_name,
         f.actual_start_date actual_start_date,
         f.actual_completion_date actual_completion_date,
         FLOOR (
            ( (f.actual_completion_date - f.actual_start_date) * 24 * 60 * 60)
            / 3600)
         || ' HOURS '
         || FLOOR (
               ( (  (f.actual_completion_date - f.actual_start_date)
                  * 24
                  * 60
                  * 60)
                - FLOOR (
                     (  (f.actual_completion_date - f.actual_start_date)
                      * 24
                      * 60
                      * 60)
                     / 3600)
                  * 3600)
               / 60)
         || ' MINUTES '
         || ROUND (
               ( (  (f.actual_completion_date - f.actual_start_date)
                  * 24
                  * 60
                  * 60)
                - FLOOR (
                     (  (f.actual_completion_date - f.actual_start_date)
                      * 24
                      * 60
                      * 60)
                     / 3600)
                  * 3600
                - (FLOOR (
                      ( (  (f.actual_completion_date - f.actual_start_date)
                         * 24
                         * 60
                         * 60)
                       - FLOOR (
                            (  (f.actual_completion_date - f.actual_start_date)
                             * 24
                             * 60
                             * 60)
                            / 3600)
                         * 3600)
                      / 60)
                   * 60)))
         || ' SECS '
            time_difference,
         DECODE (
            p.concurrent_program_name,
            'ALECDC', p.concurrent_program_name || '[' || f.description || ']',
            p.concurrent_program_name)
            concurrent_program_name,
         DECODE (f.phase_code,
                 'R', 'Running',
                 'C', 'Complete',
                 f.phase_code)
            Phase,
         f.status_code
    FROM apps.fnd_concurrent_programs p,
         apps.fnd_concurrent_programs_tl pt,
         apps.fnd_concurrent_requests f
   WHERE     f.concurrent_program_id = p.concurrent_program_id
         AND f.program_application_id = p.application_id
         AND f.concurrent_program_id = pt.concurrent_program_id
         AND f.program_application_id = pt.application_id
         AND pt.language = USERENV ('Lang')
         AND f.actual_start_date IS NOT NULL
         AND USER_CONCURRENT_PROGRAM_NAME LIKE 'XXXtest%'
ORDER BY f.actual_completion_date - f.actual_start_date DESC;

Generating Random Password

This function is used to generate random password for user

CREATE OR REPLACE FUNCTION xxhcc_generatePassword(LENGTH IN NUMBER)
RETURN VARCHAR
IS
tmpChr VARCHAR2(1);
tmpPswd VARCHAR2(32767);
tmpnum NUMBER(1);
BEGIN

FOR i IN 1 .. LENGTH
LOOP

SELECT CHR(ROUND(dbms_random.value(48,57),0)) INTO tmpnum FROM dual;
SELECT CHR(ROUND(dbms_random.value(65,90),0)) INTO tmpChr FROM dual;

tmpPswd:=tmpPswd||tmpChr||tmpnum;
END LOOP;

RETURN tmpPswd;
END;

select xxhcc_generatePassword(5) from dual;

Ans:- E9K8J6Y3H5