Sunday, 4 November 2012

API to get Customer Credit Exposure Amount in Oracle Apps



DECLARE
   V_HOLD_ID                NUMBER          DEFAULT 50;
   L_CUST_TOTAL_EXPOSURE    NUMBER;
   P_CREDIT_CHECK_RULE_ID   NUMBER;
   V_MSG_DATA               VARCHAR2 (4000);
   L_RETURN_STATUS          VARCHAR2 (30);
BEGIN
   MO_GLOBAL.INIT ('ONT');
   FND_GLOBAL.APPS_INITIALIZE (USER_ID           => 1110,
                               RESP_ID           => 50678,
                               RESP_APPL_ID      => 7000
                              );
   MO_GLOBAL.SET_POLICY_CONTEXT ('S', 83);
 
   DBMS_OUTPUT.PUT_LINE ('Calling the API to get customr exposure amount');
 
    OE_CREDIT_ENGINE_GRP.GET_CUSTOMER_EXPOSURE
       (P_CUSTOMER_ID                     => 1590,     ---- Customer Account ID
        P_SITE_ID                                   => NULL,  ---- Customer Site ID
        P_LIMIT_CURR_CODE             => 'INR',    ---- Currency Code
        P_CREDIT_CHECK_RULE_ID  => 1041,    ---- Credit_Check_Rule_ID from table OE_CREDIT_CHECK_RULES
        X_TOTAL_EXPOSURE              => L_CUST_TOTAL_EXPOSURE,
        X_RETURN_STATUS                 => L_RETURN_STATUS
       );

   IF L_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS
   THEN
      DBMS_OUTPUT.PUT_LINE ('success:');
      DBMS_OUTPUT.PUT_LINE (   'l_total_exposure_amount - '
                            || L_CUST_TOTAL_EXPOSURE
                           );
      COMMIT;
   ELSIF L_RETURN_STATUS IS NULL
   THEN
      DBMS_OUTPUT.PUT_LINE ('Status is null');
   ELSE
      DBMS_OUTPUT.PUT_LINE ('Failed: ' || V_MSG_DATA);

      FOR I IN 1 .. OE_MSG_PUB.COUNT_MSG
      LOOP
         V_MSG_DATA := OE_MSG_PUB.GET (P_MSG_INDEX => I, P_ENCODED => 'F');
         DBMS_OUTPUT.PUT_LINE (I || ') ' || V_MSG_DATA);
      END LOOP;

      ROLLBACK;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('Error is ' || SQLCODE || '---' || SQLERRM);
END;

Posted by at No comments:

Query to get Year Calendar


SELECT   LPAD (MONTH, 20 - (20 - LENGTH (MONTH)) / 2) MONTH, "Sun", "Mon",
         "Tue", "Wed", "Thu", "Fri", "Sat"
    FROM (SELECT   TO_CHAR (dt, 'fmMonthfm YYYY') MONTH,
                   TO_CHAR (dt + 1, 'iw') week,
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '1', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Sun",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '2', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Mon",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '3', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Tue",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '4', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Wed",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '5', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Thu",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '6', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Fri",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '7', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Sat"
              FROM (SELECT TRUNC (SYSDATE, 'y') - 1 + ROWNUM dt
                      FROM all_objects
                     WHERE ROWNUM <=
                                ADD_MONTHS (TRUNC (SYSDATE, 'y'), 12)
                              - TRUNC (SYSDATE, 'y'))
          GROUP BY TO_CHAR (dt, 'fmMonthfm YYYY'), TO_CHAR (dt + 1, 'iw'))
ORDER BY TO_DATE (MONTH, 'Month YYYY'), TO_NUMBER (week)
Posted by at No comments:

Tuesday, 28 August 2012

API to Create Item Cost in OPM


GMF_ITEMCOST_PUB.CREATE_ITEM_COST API is used to create item cost in Oracle Process Manufacturing.
This API creates a new item cost in the Cost Details table. [ Table = CM_CMPT_DTL]


The GMF_ITEMCOST_PUB package defines and uses the following custom PL/SQL
Record Types and INDEX BY table Types:
TYPE header_rec_type IS RECORD
(
period_id cm_cmpt_dtl.period_id%TYPE,
calendar_code cm_cldr_hdr_b.calendar_code%TYPE,
period_code cm_cldr_dtl.period_code%TYPE,
cost_type_id cm_cmpt_dtl.cost_type_id%TYPE,
cost_mthd_code cm_mthd_mst.cost_mthd_code%TYPE,
organization_id cm_cmpt_dtl.organization_id%TYPE,
organization_code mtl_parameters.organization_code%TYPE,
inventory_item_id cm_cmpt_dtl.inventory_item_id%TYPE,
item_number mtl_item_flexfields.item_number%TYPE,
user_name fnd_user.user_name%TYPE
);


TYPE this_level_dtl_rec_type IS RECORD
(
cmpntcost_id NUMBER,
cost_cmpntcls_id NUMBER,
cost_cmpntcls_code cm_cmpt_mst.cost_cmpntcls_code%TYPE,
cost_analysis_code cm_cmpt_dtl.cost_analysis_code%TYPE,
cmpnt_cost NUMBER,
burden_ind NUMBER,
total_qty NUMBER,
costcalc_orig NUMBER,
rmcalc_type NUMBER,
delete_mark NUMBER,
attribute1 cm_cmpt_dtl.attribute1%TYPE,
attribute2 cm_cmpt_dtl.attribute2%TYPE,
attribute3 cm_cmpt_dtl.attribute3%TYPE,
attribute4 cm_cmpt_dtl.attribute4%TYPE,
attribute5 cm_cmpt_dtl.attribute5%TYPE,
attribute6 cm_cmpt_dtl.attribute6%TYPE,
attribute7 cm_cmpt_dtl.attribute7%TYPE,
attribute8 cm_cmpt_dtl.attribute8%TYPE,
attribute9 cm_cmpt_dtl.attribute9%TYPE,
attribute10 cm_cmpt_dtl.attribute10%TYPE,
attribute11 cm_cmpt_dtl.attribute11%TYPE,
attribute12 cm_cmpt_dtl.attribute12%TYPE,
attribute13 cm_cmpt_dtl.attribute13%TYPE,
attribute14 cm_cmpt_dtl.attribute14%TYPE,
attribute15 cm_cmpt_dtl.attribute15%TYPE,
attribute16 cm_cmpt_dtl.attribute16%TYPE,
attribute17 cm_cmpt_dtl.attribute17%TYPE,
attribute18 cm_cmpt_dtl.attribute18%TYPE,
attribute19 cm_cmpt_dtl.attribute19%TYPE,
attribute20 cm_cmpt_dtl.attribute20%TYPE,
attribute21 cm_cmpt_dtl.attribute21%TYPE,
attribute22 cm_cmpt_dtl.attribute22%TYPE,
attribute23 cm_cmpt_dtl.attribute23%TYPE,
attribute24 cm_cmpt_dtl.attribute24%TYPE,
attribute25 cm_cmpt_dtl.attribute25%TYPE,
attribute26 cm_cmpt_dtl.attribute26%TYPE,
attribute27 cm_cmpt_dtl.attribute27%TYPE,
attribute28 cm_cmpt_dtl.attribute28%TYPE,
attribute29 cm_cmpt_dtl.attribute29%TYPE,
attribute30 cm_cmpt_dtl.attribute30%TYPE,
attribute_category cm_cmpt_dtl.attribute_category%TYPE
);

TYPE this_level_dtl_tbl_type IS TABLE OF this_level_dtl_rec_type INDEX
BY BINARY_INTEGER;
TYPE lower_level_dtl_rec_type IS RECORD
(
cmpntcost_id NUMBER,
cost_cmpntcls_id NUMBER,
cost_cmpntcls_code cm_cmpt_mst.cost_cmpntcls_code%TYPE,
cost_analysis_code cm_cmpt_dtl.cost_analysis_code%TYPE,
cmpnt_cost NUMBER,
delete_mark NUMBER
);
TYPE lower_level_dtl_tbl_type IS TABLE OF lower_level_dtl_rec_type
INDEX BY BINARY_INTEGER;


TYPE costcmpnt_ids_rec_type IS RECORD
(
cost_cmpntcls_id NUMBER,
cost_analysis_code cm_cmpt_dtl.cost_analysis_code%TYPE,
cost_level NUMBER,
cmpntcost_id NUMBER
);

TYPE costcmpnt_ids_tbl_type IS TABLE OF costcmpnt_ids_rec_type INDEX
BY BINARY_INTEGER;

PL - SQL Code for API:-


ALTER SESSION SET NLS_LANGUAGE=AMERICAN;

DECLARE
   L_STATUS          VARCHAR2 (11);
   L_RETURN_STATUS   VARCHAR2 (11)               := FND_API.G_RET_STS_SUCCESS;
   L_COUNT           NUMBER (10);
   L_RECORD_COUNT    NUMBER (10)                               := 0;
   L_LOOP_CNT        NUMBER (10)                               := 0;
   L_DUMMY_CNT       NUMBER (10)                               := 0;
   L_DATA            VARCHAR2 (3000);
   L_MSG_INDEX_OUT   NUMBER;
   L_HEADER_REC      GMF_ITEMCOST_PUB.HEADER_REC_TYPE;
   L_THIS_LVL_TBL    GMF_ITEMCOST_PUB.THIS_LEVEL_DTL_TBL_TYPE;
   L_LOWER_LVL_TBL   GMF_ITEMCOST_PUB.LOWER_LEVEL_DTL_TBL_TYPE;
   L_COSTCMPNT_IDS   GMF_ITEMCOST_PUB.COSTCMPNT_IDS_TBL_TYPE;
BEGIN
   FND_GLOBAL.APPS_INITIALIZE (USER_ID           => 1110,
                               RESP_ID           => 50687,
                               RESP_APPL_ID      => 555
                              );
                             
   L_HEADER_REC.PERIOD_ID           := 141;
   L_HEADER_REC.CALENDAR_CODE       := 'CST_CAL';
   L_HEADER_REC.PERIOD_CODE         := 'JUN 2012'; --NULL;
   L_HEADER_REC.COST_TYPE_ID        := 1000;
   L_HEADER_REC.COST_MTHD_CODE      := 'PMAC';   -- NULL;
   L_HEADER_REC.ORGANIZATION_ID     := 229;
   L_HEADER_REC.ORGANIZATION_CODE   := 'R02'; --NULL;
   L_HEADER_REC.INVENTORY_ITEM_ID   := 2005;
--L_HEADER_REC.ITEM_NUMBER          := NULL;
   L_HEADER_REC.USER_NAME           := 'USER1';
 
   L_THIS_LVL_TBL (1).CMPNTCOST_ID          := 21188;
   L_THIS_LVL_TBL (1).COST_CMPNTCLS_ID      := 1;
   L_THIS_LVL_TBL (1).COST_CMPNTCLS_CODE    := 'MATERIAL';
   L_THIS_LVL_TBL (1).COST_ANALYSIS_CODE    := 'DIR';
   L_THIS_LVL_TBL (1).CMPNT_COST            := 60;
   L_THIS_LVL_TBL (1).BURDEN_IND            := 0;
   L_THIS_LVL_TBL (1).TOTAL_QTY             := 0;
   L_THIS_LVL_TBL (1).COSTCALC_ORIG         := 1;
   L_THIS_LVL_TBL (1).RMCALC_TYPE           := 1;
   L_THIS_LVL_TBL (1).DELETE_MARK           := 0;
 
   DBMS_OUTPUT.PUT_LINE ('Start');
   GMF_ITEMCOST_PUB.CREATE_ITEM_COST
                                    (P_API_VERSION              => 3.0,
                                     P_INIT_MSG_LIST            => FND_API.G_FALSE,
                                     P_COMMIT                   => FND_API.G_FALSE,
                                     X_RETURN_STATUS            => L_STATUS,
                                     X_MSG_COUNT                => L_COUNT,
                                     X_MSG_DATA                 => L_DATA,
                                     P_HEADER_REC               => L_HEADER_REC,
                                     P_THIS_LEVEL_DTL_TBL       => L_THIS_LVL_TBL,
                                     P_LOWER_LEVEL_DTL_TBL      => L_LOWER_LVL_TBL,
                                     X_COSTCMPNT_IDS            => L_COSTCMPNT_IDS
                                    );
   DBMS_OUTPUT.PUT_LINE ('Status:' || L_STATUS);
   DBMS_OUTPUT.PUT_LINE ('Debug:' || L_COUNT);

   IF L_STATUS = 'S'
   THEN
      COMMIT;
      DBMS_OUTPUT.PUT_LINE ('success!!');
   ELSE
      IF L_COUNT = 1
      THEN
         DBMS_OUTPUT.PUT_LINE ('Error 11111 ' || L_DATA);
      ELSE
         DBMS_OUTPUT.PUT_LINE (   'status: '
                               || L_STATUS
                               || ' Error Count '
                               || L_COUNT
                              );

         FOR I IN 1 .. L_COUNT
         LOOP
            FND_MSG_PUB.GET (P_MSG_INDEX          => I,
                             P_DATA               => L_DATA,
                             P_ENCODED            => FND_API.G_FALSE,
                             P_MSG_INDEX_OUT      => L_MSG_INDEX_OUT
                            );
            DBMS_OUTPUT.PUT_LINE ('Error 2222: ' || SUBSTR (L_DATA, 1, 255));
         END LOOP;
      END IF;
   END IF;

   DBMS_OUTPUT.PUT_LINE ('end of procedure');
END;




Posted by at No comments:

API for Bank Account Creation in Oracle Apps

CE_BANK_PUB.CREATE_BANK_ACCT API is used to create a bank account for the branch party id passed as an input parameter. The API uses the BANKACCT_REC_TYPE record to pass the input values. On successful creation of bank account, the API returns the bank account id along with the information /error messages. The API returns a null bank account id if the bank branch is not created.



DECLARE

   p_init_msg_list   VARCHAR2 (200);
   p_acct_rec        apps.ce_bank_pub.bankacct_rec_type;
   x_acct_id         NUMBER;
   x_return_status   VARCHAR2 (200);
   x_msg_count       NUMBER;
   x_msg_data        VARCHAR2 (200);
   p_count           NUMBER;

BEGIN
   p_init_msg_list := NULL;
   -- HZ_PARTIES.PARTY_ID BANK BRANCH
   p_acct_rec.branch_id := 8056;
   -- HZ_PARTIES.PARTY_ID BANK
   p_acct_rec.bank_id := 8042;
   -- HZ_PARTIES.PARTY_ID ORGANIZATION
   p_acct_rec.account_owner_org_id := 23273;
   -- HZ_PARTIES.PARTY_ID Person related to ABOVE ORGANIZATION
   p_acct_rec.account_owner_party_id := 2041;

   p_acct_rec.account_classification := 'INTERNAL';
   p_acct_rec.bank_account_name := 'Test Bank Accunt';
   p_acct_rec.bank_account_num := 14256789;
   p_acct_rec.currency := 'INR';
   p_acct_rec.start_date := SYSDATE;
   p_acct_rec.end_date := NULL;

   CE_BANK_PUB.CREATE_BANK_ACCT
                  (p_init_msg_list      => p_init_msg_list,
                   p_acct_rec           => p_acct_rec,
                   x_acct_id            => x_acct_id,
                   x_return_status      => x_return_status,
                   x_msg_count          => x_msg_count,
                   x_msg_data           => x_msg_data
                  );
                       
   DBMS_OUTPUT.put_line ('X_ACCT_ID = ' || x_acct_id);
   DBMS_OUTPUT.put_line ('X_RETURN_STATUS = ' || x_return_status);
   DBMS_OUTPUT.put_line ('X_MSG_COUNT = ' || x_msg_count);
   DBMS_OUTPUT.put_line ('X_MSG_DATA = ' || x_msg_data);

   IF x_msg_count = 1
   THEN
      DBMS_OUTPUT.put_line ('x_msg_data ' || x_msg_data);
   ELSIF x_msg_count > 1
   THEN
      LOOP
         p_count := p_count + 1;
         x_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

         IF x_msg_data IS NULL
         THEN
            EXIT;
         END IF;

         DBMS_OUTPUT.put_line ('Message' || p_count || ' ---' || x_msg_data);
      END LOOP;
   END IF;
END;
Posted by at No comments:

Saturday, 9 June 2012

Open Concurrent Request Output File and Log file on button click in Custom Form in Oracle Apps R12


In order to open concurrent request output or log file in browser by clicking on button in Custom Oracle form we need to do the following steps to achieve this:
1] Open custom form in Form Builder and click on Attached Libraries. Then click on + add button.




















2] Add FNDCONC.pll library file. This library file is available in the $AU_TOP/../resource folder.























3] Now open the FNDCONC.pll library file from Resource folder in Oracle Form Builder. Inside FNDCONC.pll library there are standard program units defined by oracle.























4] In order to open submitted request output file by clicking on Button we need to use procedure report defined in EDITOR_PKG.
Syntax - EDITOR_PKG.report (req_id number, save_output_flag varchar2);
            where, req_id is request id of submitted request
                         save_output_flag is to indicate whether output file to be saved or not.

5] To open submitted request log file by clicking on Button we need to use procedure request_log defined in EDITOR_PKG.
Syntax - EDITOR_PKG .request_log(req_id number);
            where, req_id is request id of submitted request.














6] Now call the above two procedures on button’s WHEN-BUTTON-PRESSED trigger to open concurrent request log/output file.





7] Save and compile the form on server, and check the request output and log file by clicking button on custom form.


Posted by at 2 comments:

Friday, 8 June 2012

Supplier Interface in Oracle Apps R12



There are three interface tables in oracle apps Supplier Interface.
1] AP_SUPPILERS_INT
2] AP_SUPPLIER_SITES_INT
3] AP_SUP_SITE_CONTACTS_INT
Supplier Interface Programs:
1]  Supplier Open Interface Import
2]  Supplier Sites Open Interface Import
3]  Supplier Site Contacts Open Interface Import
1] AP_SUPPLIERS_INT
This is the Open interface table for Supplier used to store Supplier header information. The columns mapped to corresponding AP_SUPPLIERS table.
‘Supplier Open Interface Import’ request is used to import Supplier Header Information into Oracle.
Mandatory Columns:
            VENDOR_INTERFACE_ID  รจ AP_SUPPLIERS_INT_S.NEXTVAL [ Unique Identifier]
            VENDOR_NAME                 รจ Supplier Name
Other Important Columns:
            VENDOR_TYPE_LOOKUP_CODE รจ Supplier Type [e.g. Company etc]
            SUMMARY_FLAG                           รจ Key Flexfield Summary Flag
            ENABLED_FLAG                            รจ Key Flexfield Enable flag
            NUM_1099                                         รจ Tax identification number
            INVOICE_CURRENCY_CODE     รจ Invoice Currency Identifier
            TERMS_ID                                        รจ Payment Terms Identifier
            SEGMENT1                                      รจ Vendor Number
2] AP_SUPPLIER_SITES_INT
This is the Open interface table to import supplier sites information. The columns mapped to corresponding AP_SUPPLIER_SITES_ALL table.
‘Supplier Sites Open Interface Import’ request is used to import Supplier site Information into Oracle.
Mandatory Columns:
            VENDOR_SITE_INTERFACE_ID   รจ AP_SUPPLIER_SITES_INT_S.NEXTVAL [Unique Identifier]
            VENDOR_SITE_CODE                  รจ Supplier Site Name
            VENDOR_ID                                                รจ Vendor ID created in AP_SUPPLIERS table after submission of Supplier Open Interface Import request.

Other Important Columns:
            ADDRESS_LINE1                รจ First Line of Address
            ADDRESS_LINE2                รจ Second Line of Address
            ADDRESS_LINE3                รจ Third line of Address
            CITY                                       รจ City Name
            STATE                                   รจ State Name
            COUNTRY                            รจ Country Name
            ZIP                                          รจ Postal Code
            PHONE                                  รจ Phone Number
            FAX                                        รจ Fax Number
            EMAIL_ADDRESS              รจ Supplier Site URL
            PURCHASING_SITE_FLAGรจ To indicate purchasing allow from this site [Y/N]
            RFQ_ONLY_SITE_FLAG   รจ To indicate quotations allow from this site [Y/N]
            PAY_SITE_FLAG                รจ To indicate payable invoice allow for this site [Y/N]
            INVOICE_CURRENCY_CODEรจ Invoice Currency Identifier
            TERMS_ID                            รจ Payment Terms ID
3] AP_SUP_SITE_CONTACT_INT:
This is Open interface table for supplier site contacts. The columns mapped to corresponding AP_SUPPLIER_CONTACTS table.
Supplier Site Contacts Open Interface Import’ request is used to import Supplier site Contacts Information into Oracle.
Mandatory Columns:
            VENDOR_CONTACT_INTERFACE_ID รจ AP_SUP_SITE_CONTACT_INT_S.NEXTVAL [Unique Identifier]
            VENDOR_SITE_CODE                           รจ Supplier Site Code
Other Important Columns:
            VENDOR_ID                                        รจ Vendor_ID created in AP_SUPPLIERS table after submission of Supplier Open Interface Import request.
            VENDOR_SITE_ID                 รจ Vendor_Site_ID created in AP_SUPPLIER_SITES_ALL table after submission of Supplier Sites Open Interface Import request.
            PREFIX                                     รจ Title e.g. [Mr, Mrs, Doctor etc]
            FIRST_NAME                           รจ Contact First Name
            MIDDLE_NAME                      รจ Contact Middle Name
            LAST_NAME                                        รจ Contact Last Name
            AREA_CODE                                        รจ Area Code of Contact phone number
            PHONE                                      รจ Contact phone number
            FAX_AREA_CODE                  รจ Area Code of Contact facsimile number
            FAX                                            รจ Contact facsimile number     



Validation during Supplier Import using interface:
1] AP_SUPPLIER_INT.VENDOR_NAME
            Validation for duplicate Supplier Name in AP_SUPPLIERS table.
2] AP_SUPPLIER_INT.VENDOR_TYPE_LOOKUP_CODE
            Validations for valid Supplier Type in PO_LOOKUP_CODES table for LOOK_TYPE as ‘VENDOR TYPE’.
3] AP_SUPPLIER_INT.SEGMENT1
            Validation for duplicate Supplier Number in AP_SUPPLIERS table.
4] AP_SUPPLIER_SITES_INT.VENDOR_SITE_CODE
            Validation for duplicate Supplier Site Name in AP_SUPPLIER_SITES table.
5] AP_SUPPLIER_SITES_INT.COUNTRY
            Validation for country code in FND_TERRITORIES_VL table.

 AP_SUPPLIER_INT_REJECTIONS table contains suppliers, sites, contacts rejections information.
Posted by at 9 comments:

Wednesday, 9 May 2012

SQL Query to get list of Personlizations in Oracle Apps


SELECT ffv.form_id                 ,
  ffv.form_name                     ,
  ffv.user_form_name                ,
  ffv.description "Form Description",
  ffcr.SEQUENCE                     ,
  ffcr.description "Personalization Rule Name"
   FROM fnd_form_vl ffv,
  fnd_form_custom_rules ffcr
  WHERE ffv.form_name = ffcr.form_name
ORDER BY ffv.form_name,
  ffcr.SEQUENCE;
Posted by at No comments:

Sunday, 22 January 2012

Sub Ledger Break Up Query - Oracle Apps R-12


SELECT
  GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
  GJH.NAME JV_NAME,
  GJH.JE_CATEGORY,
  GJH.JE_SOURCE,
  GJH.PERIOD_NAME,
  NVL(XAL.ACCOUNTED_CR,0) GL_CR,
  NVL(XAL.ACCOUNTED_DR,0) GL_DR,
  GJL.DESCRIPTION JV_LINE_DESCRIPTION,
  XAH.EVENT_TYPE_CODE,
  XAH.DESCRIPTION SLA_DESCRIPTION,
  XAL.AE_LINE_NUM,
  XAL.ACCOUNTING_DATE GL_DATE,
  ASUP.VENDOR_NAME,
  TO_CHAR(ACA.CHECK_NUMBER),
  ACA.CHECK_DATE,
  ACA.DOC_SEQUENCE_VALUE VOUCHER_NUMBER,
  ACA.CREATION_DATE VOUCHER_DATE,
--  DECODE (XAH.EVENT_TYPE_CODE,'PAYMENT CANCELLED',AMOUNT* NVL(EXCHANGE_RATE,1),'REFUND RECORDED',XAL.ACCOUNTED_DR,
--   0)  RECEIPT,
--  DECODE (XAH.EVENT_TYPE_CODE,'PAYMENT CREATED',AMOUNT* NVL(EXCHANGE_RATE,1),0) PAYMENT
  DECODE(XAL.ACCOUNTED_CR,NULL,XAL.ACCOUNTED_DR,0)  RECEIPT,
  DECODE(XAL.ACCOUNTED_DR,NULL,XAL.ACCOUNTED_CR,0) PAYMENT
FROM
  XLA_AE_HEADERS XAH,
  XLA_AE_LINES XAL,
  GL_JE_LINES GJL,
  GL_IMPORT_REFERENCES GIR,
  GL_JE_HEADERS GJH,
  GL_CODE_COMBINATIONS GCC,
  AP_SUPPLIERS ASUP,
  AP_CHECKS_ALL ACA
WHERE
  XAH.AE_HEADER_ID=XAL.AE_HEADER_ID AND
  GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND
  GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND
  GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE AND
  GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND
  GJL.JE_HEADER_ID=GJH.JE_HEADER_ID  AND
  GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID AND
  ASUP.VENDOR_ID(+)=XAL.PARTY_ID AND
  ACA.DOC_SEQUENCE_ID(+)=XAH.DOC_SEQUENCE_ID AND
  ACA.DOC_SEQUENCE_VALUE(+)=XAH.DOC_SEQUENCE_VALUE AND
  GCC.SEGMENT5=NVL(:P_ACC_NUM,GCC.SEGMENT5)  AND
  TRUNC(GJH.DEFAULT_EFFECTIVE_DATE) BETWEEN NVL(:P_FROM_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND NVL(:P_TO_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND
  GJH.STATUS='P' AND
  GJH.JE_SOURCE='Payables'
UNION ALL
------ DATA FROM CASH MANAGEMENT --------------------------------
SELECT
  GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
  GJH.NAME JV_NAME,
  GJH.JE_CATEGORY,
  GJH.JE_SOURCE,
  GJH.PERIOD_NAME,
  NVL(XAL.ACCOUNTED_CR,0) GL_CR,
  NVL(XAL.ACCOUNTED_DR,0) GL_DR,
  GJL.DESCRIPTION JV_LINE_DESCRIPTION,
  XAH.EVENT_TYPE_CODE,
  XAH.DESCRIPTION SLA_DESCRIPTION,
  XAL.AE_LINE_NUM,
  XAL.ACCOUNTING_DATE GL_DATE,
  '' VENDOR_NAME,
  '' CHECK_NUMBER,
  NULL CHECK_DATE,
  NULL VOUCHER_NUMBER,
  NULL VOUCHER_DATE,
  DECODE(XAL.ACCOUNTED_CR,NULL,XAL.ACCOUNTED_DR,0)  RECEIPT,
  DECODE(XAL.ACCOUNTED_DR,NULL,XAL.ACCOUNTED_CR,0) PAYMENT
FROM
  XLA_AE_HEADERS XAH,
  XLA_AE_LINES XAL,
  GL_JE_LINES GJL,
  GL_IMPORT_REFERENCES GIR,
  GL_JE_HEADERS GJH,
  GL_CODE_COMBINATIONS GCC
WHERE
  XAH.AE_HEADER_ID=XAL.AE_HEADER_ID AND
  GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND
  GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND
  GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE AND
  GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND
  GJL.JE_HEADER_ID=GJH.JE_HEADER_ID  AND
  GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID AND
  GCC.SEGMENT5=NVL(:P_ACC_NUM,GCC.SEGMENT5)  AND
  TRUNC(GJH.DEFAULT_EFFECTIVE_DATE) BETWEEN NVL(:P_FROM_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND NVL(:P_TO_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND
 -- GJH.PERIOD_NAME IN ('APR-11-12','MAY-11-12','JUN-11-12','JUL-11-12') AND
  GJH.STATUS='P' AND
  GJH.JE_SOURCE='Cash Management' AND
  GJH.JE_CATEGORY='Bank Transfers'
UNION ALL
-------------------Data from Receivable --------------------------------
SELECT GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
      GJH.NAME JV_NAME,
      GJH.JE_CATEGORY,
      GJH.JE_SOURCE,
      GJH.PERIOD_NAME,
      NVL(XAL.ACCOUNTED_CR,0) GL_CR,
      NVL(XAL.ACCOUNTED_DR,0) GL_DR,
      GJL.DESCRIPTION JV_LINE_DESCRIPTION,
      XAH.EVENT_TYPE_CODE,
      XAH.DESCRIPTION SLA_DESCRIPTION,
      XAL.AE_LINE_NUM,
      XAL.ACCOUNTING_DATE GL_DATE,
      (SELECT AC.CUSTOMER_NAME
      FROM AR_CUSTOMERS AC WHERE AC.CUSTOMER_ID=XAL.PARTY_ID) CUSTOMER_NAME,
      (SELECT ACR.RECEIPT_NUMBER FROM AR_CASH_RECEIPTS_ALL ACR
      WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND
      ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE) RECEIPT_NUMBER,
      (SELECT ACR.RECEIPT_DATE FROM AR_CASH_RECEIPTS_ALL ACR
      WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND
      ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE
      ) RECEIPT_DATE,
      (SELECT ACR.DOC_SEQUENCE_VALUE
      FROM AR_CASH_RECEIPTS_ALL ACR
      WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND
      ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE) VOUCHER_NUMBER,
      (SELECT ACR.CREATION_DATE
      FROM AR_CASH_RECEIPTS_ALL ACR
      WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND
      ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE)  VOUCHER_DATE,
      DECODE(XAL.ACCOUNTED_CR,NULL,XAL.ACCOUNTED_DR,0)  RECEIPT,
      DECODE(XAL.ACCOUNTED_DR,NULL,XAL.ACCOUNTED_CR,0) PAYMENT
  FROM GL_JE_BATCHES GJB,
       GL_JE_HEADERS GJH,
       GL_JE_LINES GJL,
       GL_CODE_COMBINATIONS GCC,
       GL_IMPORT_REFERENCES GIR,
       XLA_AE_LINES XAL,
       XLA_AE_HEADERS XAH,
       XLA.XLA_TRANSACTION_ENTITIES XTE
WHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
   AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
   AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
   AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
   AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
   AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
   AND XTE.APPLICATION_ID = XAH.APPLICATION_ID
   AND XTE.ENTITY_ID = XAH.ENTITY_ID
   AND GJL.STATUS = 'P'
   AND GCC.SEGMENT5 = NVL (:P_ACC_NUM, GCC.SEGMENT5)
   AND TRUNC (GJH.DEFAULT_EFFECTIVE_DATE)
          BETWEEN NVL (:P_FROM_DATE, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))
              AND NVL (:P_TO_DATE, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))
  AND GJH.JE_SOURCE = 'Receivables'
  UNION ALL
 ---------------- Manual -----------------------
SELECT GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
       GJH.NAME JV_NAME,
       GJH.JE_CATEGORY,
       GJH.JE_SOURCE,
       GJH.PERIOD_NAME,
       NVL(GJL.ACCOUNTED_DR,0) ACCOUNTED_DR,
       NVL(GJL.ACCOUNTED_CR,0) ACCOUNTED_CR,
       gjl.description jv_line_description,
        '' EVENT_TYPE_CODE,
        '' SLA_DESCRIPTION,
        NULL AE_LINE_NUM,
        GJH.DEFAULT_EFFECTIVE_DATE GL_DATE,
        '' VENDOR_NAME,
        '' CHECK_NUMBER,
        NULL CHECK_DATE,
        NULL VOUCHER_NUMBER,
        NULL VOUCHER_DATE,
        NVL(GJL.ACCOUNTED_DR,0) RECEIPT,
        NVL(GJL.ACCOUNTED_CR,0) PAYMENT
 FROM GL_JE_BATCHES GJB,
      GL_JE_HEADERS GJH,
      GL_JE_LINES GJL,
      GL_CODE_COMBINATIONS GCC
 WHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
   AND GJL.STATUS = 'P'
   AND GCC.SEGMENT5 = NVL (:P_ACC_NUM, GCC.SEGMENT5)
   AND TRUNC (GJH.DEFAULT_EFFECTIVE_DATE)
          BETWEEN NVL (:P_FROM_DATE, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))
              AND NVL (:P_TO_DATE, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))
   AND GJH.JE_SOURCE = 'Manual'
UNION ALL
-----ALL OTHER SOURCES OTHER THAN ABOVE----------
SELECT
GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
  GJH.NAME JV_NAME,
  GJH.JE_CATEGORY,
  GJH.JE_SOURCE,
  GJH.PERIOD_NAME,
  NVL(XAL.ACCOUNTED_CR,0) GL_CR,
  NVL(XAL.ACCOUNTED_DR,0) GL_DR,
  GJL.DESCRIPTION JV_LINE_DESCRIPTION,
  XAH.EVENT_TYPE_CODE,
  XAH.DESCRIPTION SLA_DESCRIPTION,
  XAL.AE_LINE_NUM,
  XAL.ACCOUNTING_DATE GL_DATE,
  '' VENDOR_NAME,
  '' CHECK_NUMBER,
  NULL CHECK_DATE,
  NULL VOUCHER_NUMBER,
  NULL VOUCHER_DATE,
  DECODE(XAL.ACCOUNTED_CR,NULL,XAL.ACCOUNTED_DR,0)  RECEIPT,
  DECODE(XAL.ACCOUNTED_DR,NULL,XAL.ACCOUNTED_CR,0) PAYMENT
FROM
  XLA_AE_HEADERS XAH,
  XLA_AE_LINES XAL,
  GL_JE_LINES GJL,
  GL_IMPORT_REFERENCES GIR,
  GL_JE_HEADERS GJH,
  GL_CODE_COMBINATIONS GCC
WHERE
  XAH.AE_HEADER_ID=XAL.AE_HEADER_ID AND
  GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND
  GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND
  GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE AND
  GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND
  GJL.JE_HEADER_ID=GJH.JE_HEADER_ID  AND
  GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID AND
  GCC.SEGMENT5=NVL(:P_ACC_NUM,GCC.SEGMENT5)  AND
  TRUNC(GJH.DEFAULT_EFFECTIVE_DATE) BETWEEN NVL(:P_FROM_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND NVL(:P_TO_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND
  GJH.STATUS='P' AND
  GJH.JE_SOURCE NOT IN ('Receivables','Payables','Cash Management')
Posted by at 5 comments:

GL to AR (Receivable) Query -R12

Q1:- -------------------------------------------------------------------------------------------------
SELECT GJH.NAME,
        GJH.DESCRIPTION,
        TO_CHAR(GJH.DEFAULT_EFFECTIVE_DATE, 'dd-MON-yyyy') EFF_DATE,
        (SELECT HP.PARTY_NAME
            FROM RA_CUSTOMER_TRX_ALL RCTA
            , HZ_CUST_ACCOUNTS_ALL HCA
            , HZ_PARTIES HP
            WHERE RCTA.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
            AND HCA.PARTY_ID = HP.PARTY_ID
            AND RCTA.CUSTOMER_TRX_ID = XTE.SOURCE_ID_INT_1) PARTY,
           (SELECT NVL(TO_CHAR(RCTA.DOC_SEQUENCE_VALUE),RCTA.TRX_NUMBER)
                FROM RA_CUSTOMER_TRX_ALL RCTA
                WHERE RCTA.CUSTOMER_TRX_ID = XTE.SOURCE_ID_INT_1
           ) DOC_SEQUENCE_VALUE,
       GJH.JE_CATEGORY,
       XAL.ACCOUNTED_DR ACCOUNTED_DR,
       XAL.ACCOUNTED_CR ACCOUNTED_CR,
       GJL.JE_HEADER_ID,
       XAL.PARTY_TYPE_CODE,
       GJH.JE_SOURCE,
       GJH.PERIOD_NAME,
       GCC.SEGMENT5,
       GJL.JE_LINE_NUM,
       GJH.DEFAULT_EFFECTIVE_DATE
  FROM GL_JE_BATCHES GJB,
       GL_JE_HEADERS GJH,
       GL_JE_LINES GJL,
       GL_CODE_COMBINATIONS GCC,
       GL_IMPORT_REFERENCES GIR,
       XLA_AE_LINES XAL,
       XLA_AE_HEADERS XAH,
       XLA.XLA_TRANSACTION_ENTITIES XTE
WHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
   AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
   AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
   AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
   AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
   AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
   AND XTE.APPLICATION_ID = XAH.APPLICATION_ID
   AND XTE.ENTITY_ID = XAH.ENTITY_ID
   AND GJL.STATUS = 'P'
   AND GCC.SEGMENT5 = NVL (:ACCOUNT_ID, GCC.SEGMENT5)
   AND TRUNC (GJH.DEFAULT_EFFECTIVE_DATE)
          BETWEEN NVL (:PERIOD_FROM, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))
              AND NVL (:PERIOD_TO, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))
  AND GJH.JE_SOURCE = 'Receivables'
----------------------------------------------------------------------------------------------------------
Q2:- --------------------------------------------------------------------------------------------------


SELECT GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
      GJH.NAME JV_NAME,
      GJH.JE_CATEGORY,
      GJH.JE_SOURCE,
      GJH.PERIOD_NAME,
      NVL(XAL.ACCOUNTED_CR,0) GL_CR,
      NVL(XAL.ACCOUNTED_DR,0) GL_DR,
      GJL.DESCRIPTION JV_LINE_DESCRIPTION,
      XAH.EVENT_TYPE_CODE,
      XAH.DESCRIPTION SLA_DESCRIPTION,
      XAL.AE_LINE_NUM,
      XAL.ACCOUNTING_DATE GL_DATE,
      (SELECT AC.CUSTOMER_NAME
      FROM AR_CUSTOMERS AC WHERE AC.CUSTOMER_ID=XAL.PARTY_ID) CUSTOMER_NAME,
      (SELECT ACR.RECEIPT_NUMBER FROM AR_CASH_RECEIPTS_ALL ACR
      WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND
      ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE) RECEIPT_NUMBER,
      (SELECT ACR.RECEIPT_DATE FROM AR_CASH_RECEIPTS_ALL ACR
      WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND
      ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE
      ) RECEIPT_DATE,
      (SELECT ACR.DOC_SEQUENCE_VALUE
      FROM AR_CASH_RECEIPTS_ALL ACR
      WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND
      ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE) VOUCHER_NUMBER,
      (SELECT ACR.CREATION_DATE
      FROM AR_CASH_RECEIPTS_ALL ACR
      WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND
      ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE)  VOUCHER_DATE,
      DECODE(XAL.ACCOUNTED_CR,NULL,XAL.ACCOUNTED_DR,0)  RECEIPT,
      DECODE(XAL.ACCOUNTED_DR,NULL,XAL.ACCOUNTED_CR,0) PAYMENT
  FROM GL_JE_BATCHES GJB,
       GL_JE_HEADERS GJH,
       GL_JE_LINES GJL,
       GL_CODE_COMBINATIONS GCC,
       GL_IMPORT_REFERENCES GIR,
       XLA_AE_LINES XAL,
       XLA_AE_HEADERS XAH,
       XLA.XLA_TRANSACTION_ENTITIES XTE
WHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
   AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
   AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
   AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
   AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
   AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
   AND XTE.APPLICATION_ID = XAH.APPLICATION_ID
   AND XTE.ENTITY_ID = XAH.ENTITY_ID
   AND GJL.STATUS = 'P'
   AND GCC.SEGMENT5 = NVL (:P_ACC_NUM, GCC.SEGMENT5)
   AND TRUNC (GJH.DEFAULT_EFFECTIVE_DATE)
          BETWEEN NVL (:P_FROM_DATE, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))
              AND NVL (:P_TO_DATE, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))
  AND GJH.JE_SOURCE = 'Receivables'

Posted by at 2 comments:

GL to AP (Payable) Query....

Q1:--------------------------------------------------------------------------------------------
SELECT GJH.NAME,
       GJH.DESCRIPTION,
       TO_CHAR(GJH.DEFAULT_EFFECTIVE_DATE, 'dd-MON-yyyy') EFF_DATE,
       DECODE (XTE.ENTITY_CODE,
               'AP_INVOICES', PV.VENDOR_NAME,
               (SELECT AC.VENDOR_NAME
                  FROM AP_CHECKS_ALL AC
                 WHERE XTE.SOURCE_ID_INT_1 = AC.CHECK_ID)
       ) PARTY,
       AIA.INVOICE_NUM DOC_SEQUENCE_VALUE,
       GJH.JE_CATEGORY,
       XAL.ACCOUNTED_DR ACCOUNTED_DR,
       XAL.ACCOUNTED_CR ACCOUNTED_CR,
       GJL.JE_HEADER_ID,
       XAL.PARTY_TYPE_CODE,
       GJH.JE_SOURCE,
       GJH.PERIOD_NAME,
       GCC.SEGMENT5,
       GJL.JE_LINE_NUM,
       GJH.DEFAULT_EFFECTIVE_DATE
  FROM GL_JE_BATCHES GJB,
       GL_JE_HEADERS GJH,
       GL_JE_LINES GJL,
       GL_CODE_COMBINATIONS GCC,
       GL_IMPORT_REFERENCES GIR,
       XLA_AE_LINES XAL,
       XLA_AE_HEADERS XAH,
       XLA.XLA_TRANSACTION_ENTITIES XTE,
       AP_INVOICES_ALL AIA,
       PO_VENDORS PV
 WHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
   AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
   AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
   AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
   AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
   AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
   AND XTE.APPLICATION_ID = XAH.APPLICATION_ID
   AND XTE.ENTITY_ID = XAH.ENTITY_ID
   AND AIA.INVOICE_ID(+) = XTE.SOURCE_ID_INT_1
   AND AIA.VENDOR_ID = PV.VENDOR_ID(+)
   AND GJL.STATUS = 'P'
   AND GCC.SEGMENT5 = NVL (:ACCOUNT_ID, GCC.SEGMENT5)
   AND TRUNC (GJH.DEFAULT_EFFECTIVE_DATE)
          BETWEEN NVL (:PERIOD_FROM, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))
              AND NVL (:PERIOD_TO, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))
  AND GJH.JE_SOURCE = 'Payables'
-------------------------------------------------------------------------------------------------------
Q2:- --------------------------------------------------------------------------------------------------

SELECT
  GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
  GJH.NAME JV_NAME,
  GJH.JE_CATEGORY,
  GJH.JE_SOURCE,
  GJH.PERIOD_NAME,
  NVL(XAL.ACCOUNTED_CR,0) GL_CR,
  NVL(XAL.ACCOUNTED_DR,0) GL_DR,
  GJL.DESCRIPTION JV_LINE_DESCRIPTION,
  XAH.EVENT_TYPE_CODE,
  XAH.DESCRIPTION SLA_DESCRIPTION,
  XAL.AE_LINE_NUM,
  XAL.ACCOUNTING_DATE GL_DATE,
  ASUP.VENDOR_NAME,
  TO_CHAR(ACA.CHECK_NUMBER),
  ACA.CHECK_DATE,
  ACA.DOC_SEQUENCE_VALUE VOUCHER_NUMBER,
  ACA.CREATION_DATE VOUCHER_DATE,
  DECODE(XAL.ACCOUNTED_CR,NULL,XAL.ACCOUNTED_DR,0)  RECEIPT,
  DECODE(XAL.ACCOUNTED_DR,NULL,XAL.ACCOUNTED_CR,0) PAYMENT
FROM
  XLA_AE_HEADERS XAH,
  XLA_AE_LINES XAL,
  GL_JE_LINES GJL,
  GL_IMPORT_REFERENCES GIR,
  GL_JE_HEADERS GJH,
  GL_CODE_COMBINATIONS GCC,
  AP_SUPPLIERS ASUP,
  AP_CHECKS_ALL ACA
WHERE
  XAH.AE_HEADER_ID=XAL.AE_HEADER_ID AND
  GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND
  GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND
  GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE AND
  GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND
  GJL.JE_HEADER_ID=GJH.JE_HEADER_ID  AND
  GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID AND
  ASUP.VENDOR_ID(+)=XAL.PARTY_ID AND
  ACA.DOC_SEQUENCE_ID(+)=XAH.DOC_SEQUENCE_ID AND
  ACA.DOC_SEQUENCE_VALUE(+)=XAH.DOC_SEQUENCE_VALUE AND
  GCC.SEGMENT5=NVL(:P_ACC_NUM,GCC.SEGMENT5)  AND
  TRUNC(GJH.DEFAULT_EFFECTIVE_DATE) BETWEEN NVL(:P_FROM_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND NVL(:P_TO_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND
  GJH.STATUS='P' AND
  GJH.JE_SOURCE='Payables'
Posted by at No comments:
Subscribe to: Posts (Atom)