AR Receipt Final Query

Query for  AR Receipts

PURPOSE: THE VIEW AR_CASH_RECEIPTS_V IS USED IN THE RECEIPTS GATEWAY AS THE BASETABLE FOR THE RECEIPTS FORM

/* Formatted on 08-12-2022 13:53:30 */ SELECT /* PURPOSE: THE VIEW AR_CASH_RECEIPTS_V IS USED IN THE RECEIPTS GATEWAY AS THE BASETABLE FOR THE RECEIPTS FORM */       HOU.NAME OPERATING_UNIT,       --CR .ROWID ROW_ID,          CR.CASH_RECEIPT_ID CASH_RECEIPT_ID,          CRH_CURRENT.CASH_RECEIPT_HISTORY_ID CASH_RECEIPT_HISTORY_ID,          CR.AMOUNT AMOUNT,          CRH_CURRENT.ACCTD_AMOUNT FUNCTIONAL_AMOUNT,          CRH_CURRENT.AMOUNT NET_AMOUNT,          CR.CURRENCY_CODE CURRENCY_CODE,          CR.RECEIPT_NUMBER RECEIPT_NUMBER,          CR.RECEIPT_DATE RECEIPT_DATE,          CR.ANTICIPATED_CLEARING_DATE ANTICIPATED_CLEARING_DATE,          CR.ACTUAL_VALUE_DATE ACTUAL_VALUE_DATE,          CR.TYPE TYPE,          CR.STATUS RECEIPT_STATUS,          ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('CHECK_STATUS', CR.STATUS)             RECEIPT_STATUS_DSP,          CR.COMMENTS COMMENTS,          CR.MISC_PAYMENT_SOURCE MISC_PAYMENT_SOURCE,          CR.EXCHANGE_RATE EXCHANGE_RATE,          CR.EXCHANGE_DATE EXCHANGE_RATE_DATE,          CR.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE,          GL_DCT.USER_CONVERSION_TYPE EXCHANGE_RATE_TYPE_DSP,          CR.DOC_SEQUENCE_ID DOC_SEQUENCE_ID,          CR.DOC_SEQUENCE_VALUE DOCUMENT_NUMBER,          CR.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE,          CR.CUSTOMER_RECEIPT_REFERENCE CUSTOMER_RECEIPT_REFERENCE,          REC_METHOD.NAME PAYMENT_METHOD_DSP,          REC_METHOD.PAYMENT_TYPE_CODE PAYMENT_TYPE_CODE,          CR.RECEIPT_METHOD_ID RECEIPT_METHOD_ID,          RC.NAME RECEIPT_CLASS_DSP,          RC.BILL_OF_EXCHANGE_FLAG BILL_OF_EXCHANGE_FLAG,          ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('RECEIPT_CREATION_METHOD',                                                 RC.CREATION_METHOD_CODE)             CREATION_METHOD_DSP,          RC.CREATION_METHOD_CODE CREATION_METHOD_CODE,          CR.PAY_FROM_CUSTOMER CUSTOMER_ID,          SUBSTRB (PARTY.PARTY_NAME, 1, 50) CUSTOMER_NAME,          CUST.ACCOUNT_NUMBER CUSTOMER_NUMBER,          PARTY.JGZZ_FISCAL_CODE TAXPAYER_ID,          SITE_USES.LOCATION LOCATION,          CR.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID,          CR.CUSTOMER_BANK_ACCOUNT_ID CUSTOMER_BANK_ACCOUNT_ID,          CR.CUSTOMER_BANK_BRANCH_ID CUSTOMER_BANK_BRANCH_ID,          DECODE (             NVL (FND_PROFILE.VALUE ('AR_MASK_BANK_ACCOUNT_NUMBERS'), 'F'),             'N',             CUST_BANK.BANK_ACCOUNT_NUM,             'F',             RPAD (SUBSTR (CUST_BANK.BANK_ACCOUNT_NUM, 1, 4),                   LENGTH (CUST_BANK.BANK_ACCOUNT_NUM),                   '*'),             'L',             LPAD (SUBSTR (CUST_BANK.BANK_ACCOUNT_NUM, -4),                   LENGTH (CUST_BANK.BANK_ACCOUNT_NUM),                   '*')          )             CUSTOMER_BANK_ACCOUNT,          CUST_BANK.BANK_ACCOUNT_NUM CUSTOMER_BANK_ACCOUNT_NUM,          NVL (CUST_BANK_BRANCH.BANK_NAME, CUST_BANK_BRANCH2.BANK_NAME)             CUSTOMER_BANK_NAME,          NVL (CUST_BANK_BRANCH.BANK_BRANCH_NAME,               CUST_BANK_BRANCH2.BANK_BRANCH_NAME)             CUSTOMER_BANK_BRANCH,          CRH_FIRST_POSTED.BATCH_ID BATCH_ID,          DECODE (RC.CREATION_METHOD_CODE, 'BR', BAT_BR.NAME, BAT.NAME)             BATCH_NAME                       ,          DIST_SET.DISTRIBUTION_SET_NAME DISTRIBUTION_SET,          CR.DISTRIBUTION_SET_ID DISTRIBUTION_SET_ID,          CR.DEPOSIT_DATE DEPOSIT_DATE,          CR.REFERENCE_TYPE REFERENCE_TYPE,          CR.VAT_TAX_ID VAT_TAX_ID,          VAT.TAX_CODE TAX_CODE,          CR.TAX_RATE TAX_RATE,          VAT.TAX_ACCOUNT_ID,          VAT.VALIDATE_FLAG ADHOC_FLAG,          L_REF_TYPE.MEANING REFERENCE_TYPE_DSP,          CR.REFERENCE_ID REFERENCE_ID,          CR.REMITTANCE_BANK_ACCOUNT_ID REMITTANCE_BANK_ACCOUNT_ID,          REMIT_BANK.BANK_ACCOUNT_NUM REMIT_BANK_ACCOUNT,          REMIT_BANK_BRANCH.BANK_NAME REMIT_BANK_NAME,          REMIT_BANK_BRANCH.BANK_BRANCH_ID REMITTANCE_BANK_BRANCH_ID,          REMIT_BANK_BRANCH.BANK_BRANCH_NAME REMIT_BANK_BRANCH,          REMIT_BANK.CURRENCY_CODE REMIT_BANK_CURRENCY,          CRH_CURRENT.FACTOR_DISCOUNT_AMOUNT FACTOR_DISCOUNT_AMOUNT,          PS.DUE_DATE MATURITY_DATE,          PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID,          ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('RECEIPT_CREATION_STATUS',                                                 CRH_CURRENT.STATUS)             STATE_DSP,          CRH_CURRENT.STATUS STATE,          CRH_CURRENT.GL_POSTED_DATE POSTED_DATE,          REC_TRX.NAME ACTIVITY,          REC_TRX.TAX_CODE_SOURCE TAX_CODE_SOURCE,          CR.RECEIVABLES_TRX_ID RECEIVABLES_TRX_ID,          CRH_CURRENT.GL_POSTED_DATE GL_POSTED_DATE,          CRH_CURRENT.POSTING_CONTROL_ID POSTING_CONTROL_ID,          CRH_FIRST_POSTED.GL_DATE GL_DATE,          CR.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY,          CR.REVERSAL_DATE REVERSAL_DATE,          DECODE (CR.REVERSAL_CATEGORY, NULL, NULL, L_REV_CAT.MEANING)             REVERSAL_CATEGORY_DSP,          CR.REVERSAL_CATEGORY REVERSAL_CATEGORY,          DECODE (CR.REVERSAL_CATEGORY, NULL, NULL, L_REV_CAT.DESCRIPTION)             CATEGORY_DESCRIPTION,          CR.REVERSAL_COMMENTS REVERSAL_COMMENTS,          DECODE (CR.REVERSAL_REASON_CODE, NULL, NULL, L_REV_REASON.MEANING)             REVERSAL_REASON,          CR.REVERSAL_REASON_CODE REVERSAL_REASON_CODE,          DECODE (CR.REVERSAL_REASON_CODE,                  NULL, NULL,                  L_REV_REASON.DESCRIPTION)             REVERSAL_REASON_DESCRIPTION,          REM_BAT.NAME REMIT_BATCH,          REM_BAT.BATCH_ID REMIT_BATCH_ID,          NVL (CR.OVERRIDE_REMIT_ACCOUNT_FLAG, 'Y') OVERRIDE_REMIT_BANK,          NVL (- (PS.AMOUNT_APPLIED), 0) APPLIED_AMOUNT,          CR.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID,          CR.PROGRAM_ID PROGRAM_ID,          CR.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE,          DECODE (             NVL (CR.CONFIRMED_FLAG, 'Y'),             'Y',             DECODE (                CR.REVERSAL_DATE,                NULL,                DECODE (                   CRH_CURRENT.STATUS,                   'REVERSED',                   'N',                   DECODE (                      CRH_CURRENT.FACTOR_FLAG,                      'Y',                      DECODE (CRH_CURRENT.STATUS, 'RISK_ELIMINATED', 'N', 'Y'),                      DECODE (CRH_CURRENT.STATUS, 'CLEARED', 'N', 'Y')                   )                ),                'N'             ),             'N'          )             AT_RISK,          REM_BAT.REMIT_METHOD_CODE REMITTANCE_METHOD,          CR.ISSUER_NAME,          CR.ISSUE_DATE,          CR.ISSUER_BANK_BRANCH_ID,          NOTES_BANK.BANK_NAME,          NOTES_BANK.BANK_BRANCH_NAME,          CRH_CURRENT.NOTE_STATUS,          CRH_NOTE_STATUS.MEANING,          CRH_NOTE_STATUS.DESCRIPTION,          RC.NOTES_RECEIVABLE,          CR.PAYMENT_SERVER_ORDER_NUM,          CR.APPROVAL_CODE,          CR.ADDRESS_VERIFICATION_CODE,          PS.CONS_INV_ID,          CR.POSTMARK_DATE POSTMARK_DATE   FROM   AP_BANK_BRANCHES NOTES_BANK,          AP_BANK_ACCOUNTS_ALL REMIT_BANK,          AP_BANK_BRANCHES REMIT_BANK_BRANCH,          AP_BANK_BRANCHES CUST_BANK_BRANCH,          AP_BANK_BRANCHES CUST_BANK_BRANCH2,          AP_BANK_ACCOUNTS_ALL CUST_BANK,          AR_VAT_TAX VAT,          HZ_CUST_ACCOUNTS CUST,          HZ_PARTIES PARTY,          AR_RECEIPT_METHODS REC_METHOD,          AR_RECEIPT_CLASSES RC,          HZ_CUST_SITE_USES_ALL SITE_USES,          AR_LOOKUPS CRH_NOTE_STATUS,          AR_LOOKUPS L_REV_CAT,          AR_LOOKUPS L_REV_REASON,          AR_LOOKUPS L_REF_TYPE,          GL_DAILY_CONVERSION_TYPES GL_DCT,          AR_CASH_RECEIPT_HISTORY_ALL CRH_REM,          AR_BATCHES REM_BAT,          AR_RECEIVABLES_TRX_ALL REC_TRX,          AR_DISTRIBUTION_SETS_ALL DIST_SET,          AR_PAYMENT_SCHEDULES_ALL PS,          AR_CASH_RECEIPT_HISTORY_ALL CRH_CURRENT,         /* CURRENT_RECORD */          AR_BATCHES_ALL BAT,          AR_BATCHES_ALL BAT_BR, /*  J Rautiainen BR Implementation */          AR_CASH_RECEIPTS_ALL CR,          AR_CASH_RECEIPT_HISTORY_ALL CRH_FIRST_POSTED, /* FIRST_POSTED_RECORD */          HR_OPERATING_UNITS HOU  WHERE       CR.PAY_FROM_CUSTOMER = CUST.CUST_ACCOUNT_ID(+)          AND CUST.PARTY_ID = PARTY.PARTY_ID(+)          AND CRH_NOTE_STATUS.LOOKUP_TYPE(+) = 'AR_NOTE_STATUS'          AND CRH_NOTE_STATUS.LOOKUP_CODE(+) = CRH_CURRENT.NOTE_STATUS          AND NOTES_BANK.BANK_BRANCH_ID(+) = CR.ISSUER_BANK_BRANCH_ID          AND REMIT_BANK.BANK_ACCOUNT_ID(+) = CR.REMITTANCE_BANK_ACCOUNT_ID          AND REMIT_BANK.BANK_BRANCH_ID = REMIT_BANK_BRANCH.BANK_BRANCH_ID(+)          AND CUST_BANK.BANK_ACCOUNT_ID(+) = CR.CUSTOMER_BANK_ACCOUNT_ID          AND CUST_BANK.BANK_BRANCH_ID = CUST_BANK_BRANCH.BANK_BRANCH_ID(+)          AND CR.CUSTOMER_BANK_BRANCH_ID = CUST_BANK_BRANCH2.BANK_BRANCH_ID(+)          AND VAT.VAT_TAX_ID(+) = CR.VAT_TAX_ID          AND CR.RECEIPT_METHOD_ID = REC_METHOD.RECEIPT_METHOD_ID          AND REC_METHOD.RECEIPT_CLASS_ID = RC.RECEIPT_CLASS_ID          AND CR.CUSTOMER_SITE_USE_ID = SITE_USES.SITE_USE_ID(+)          AND CR.RECEIVABLES_TRX_ID = REC_TRX.RECEIVABLES_TRX_ID(+)          AND CR.DISTRIBUTION_SET_ID = DIST_SET.DISTRIBUTION_SET_ID(+)          AND L_REV_CAT.LOOKUP_TYPE(+) = 'REVERSAL_CATEGORY_TYPE'          AND L_REV_CAT.LOOKUP_CODE(+) = CR.REVERSAL_CATEGORY          AND L_REV_REASON.LOOKUP_TYPE(+) = 'CKAJST_REASON'          AND L_REV_REASON.LOOKUP_CODE(+) = CR.REVERSAL_REASON_CODE          AND L_REF_TYPE.LOOKUP_CODE(+) = CR.REFERENCE_TYPE          AND L_REF_TYPE.LOOKUP_TYPE(+) = 'CB_REFERENCE_TYPE'          AND GL_DCT.CONVERSION_TYPE(+) = CR.EXCHANGE_RATE_TYPE          AND CRH_REM.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID          AND NOT EXISTS                (SELECT   CASH_RECEIPT_HISTORY_ID                   FROM   AR_CASH_RECEIPT_HISTORY_ALL CRH3                  WHERE   CRH3.STATUS = 'REMITTED'                          AND CRH3.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID                          AND CRH3.CASH_RECEIPT_HISTORY_ID <                                CRH_REM.CASH_RECEIPT_HISTORY_ID)          AND CRH_REM.STATUS(+) = 'REMITTED'          AND CRH_REM.BATCH_ID = REM_BAT.BATCH_ID(+)          AND REM_BAT.TYPE(+) = 'REMITTANCE'          AND PS.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID          AND CRH_CURRENT.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID          AND CRH_CURRENT.CURRENT_RECORD_FLAG = NVL ('Y', CR.RECEIPT_NUMBER)          AND CRH_FIRST_POSTED.BATCH_ID = BAT.BATCH_ID(+)          AND BAT.TYPE(+) = 'MANUAL'          AND CRH_FIRST_POSTED.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID          AND CRH_FIRST_POSTED.FIRST_POSTED_RECORD_FLAG(+) = 'Y'          AND CRH_FIRST_POSTED.BATCH_ID = BAT_BR.BATCH_ID(+)          AND BAT_BR.TYPE(+) = 'BR'          AND HOU.ORGANIZATION_ID=CR.ORG_ID          and HOU.ORGANIZATION_ID= &org_id          --AND TRUNC(CR.RECEIPT_DATE) BETWEEN '01-Jan-2019' AND '02-jan-2019'          AND TRUNC(CRH_FIRST_POSTED.GL_DATE) BETWEEN '01-Jan-2019' AND '02-jan-2019'          --and cr.STATUS not in ('REV', 'NSF');

API to create and update sale order price list in oracle apps

API to create and update sale order price list in oracle apps

In this post , we are going to write a PLSQL code and oracle API to Create and update sale order price list in oracle apps. Using this Below script you are Able to Create and Update Sale Orders Prices List in Oracle Apps.

CREATE OR REPLACE PROCEDURE APPS.xx_pricelist ( p_list_header_id IN NUMBER, p_operand IN NUMBER, p_product_attr_value IN VARCHAR2, p_uom IN VARCHAR2 ) IS v_return_status VARCHAR2 (1) := NULL; v_msg_count NUMBER := 0; v_msg_data VARCHAR2 (2000); v_price_list_rec qp_price_list_pub.price_list_rec_type; v_price_list_val_rec qp_price_list_pub.price_list_val_rec_type; v_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type; v_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type; v_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type; v_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type; v_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type; v_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type; ppr_price_list_rec qp_price_list_pub.price_list_rec_type; ppr_price_list_val_rec qp_price_list_pub.price_list_val_rec_type; ppr_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type; ppr_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type; ppr_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type; ppr_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type; ppr_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type; ppr_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type; BEGIN v_price_list_rec.list_header_id := p_list_header_id; v_price_list_rec.list_type_code := 'PRL'; v_price_list_rec.operation := qp_globals.g_opr_update; v_price_list_line_tbl (1).list_header_id := p_list_header_id; v_price_list_line_tbl (1).list_line_id := fnd_api.g_miss_num; v_price_list_line_tbl (1).list_line_type_code := 'PLL'; v_price_list_line_tbl (1).operation := qp_globals.g_opr_create; v_price_list_line_tbl (1).operand := p_operand; v_price_list_line_tbl (1).arithmetic_operator := 'UNIT_PRICE'; -- v_price_list_line_tbl (1).start_date_active := '05-OCT-2011'; -- v_price_list_line_tbl (1).start_date_active := '05-OCT-2011'; -- v_price_list_line_tbl (1).organization_id := NULL; v_pricing_attr_tbl (1).pricing_attribute_id := fnd_api.g_miss_num; v_pricing_attr_tbl (1).list_line_id := fnd_api.g_miss_num; ---- v_pricing_attr_tbl (1).product_attribute_context := 'Item'; -- v_pricing_attr_tbl (1).product_attribute := 'Item Number'; v_pricing_attr_tbl (1).product_attribute_context := 'ITEM'; v_pricing_attr_tbl (1).product_attribute := 'PRICING_ATTRIBUTE1'; v_pricing_attr_tbl (1).product_attr_value := p_product_attr_value; v_pricing_attr_tbl (1).product_uom_code := p_uom; v_pricing_attr_tbl (1).excluder_flag := 'N'; v_pricing_attr_tbl (1).attribute_grouping_no := 1; v_pricing_attr_tbl (1).price_list_line_index := 1; v_pricing_attr_tbl (1).operation := qp_globals.g_opr_create; DBMS_OUTPUT.put_line ('Calling API to insert Price List'); qp_price_list_pub.process_price_list (p_api_version_number => 1, p_init_msg_list => fnd_api.g_true, p_return_values => fnd_api.g_false, p_commit => fnd_api.g_false, x_return_status => v_return_status, x_msg_count => v_msg_count, x_msg_data => v_msg_data, p_price_list_rec => v_price_list_rec, p_price_list_line_tbl => v_price_list_line_tbl, p_pricing_attr_tbl => v_pricing_attr_tbl, x_price_list_rec => ppr_price_list_rec, x_price_list_val_rec => ppr_price_list_val_rec, x_price_list_line_tbl => ppr_price_list_line_tbl, x_qualifiers_tbl => ppr_qualifiers_tbl, x_qualifiers_val_tbl => ppr_qualifiers_val_tbl, x_pricing_attr_tbl => ppr_pricing_attr_tbl, x_pricing_attr_val_tbl => ppr_pricing_attr_val_tbl, x_price_list_line_val_tbl => ppr_price_list_line_val_tbl ); IF v_return_status = fnd_api.g_ret_sts_success THEN COMMIT; DBMS_OUTPUT.put_line ('The Item loading into the price list is Sucessfull'); ELSE DBMS_OUTPUT.put_line ('The Item loading into the price list Failed'); ROLLBACK; FOR i IN 1 .. v_msg_count 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; END IF; END; /

After this you need to create one custom table

CREATE TABLE APPS.XXPRICE_LST_TBL ( ORG_ID NUMBER, PRICE_LIST VARCHAR2(400 BYTE), ITEM_CODE VARCHAR2(400 BYTE), ATTR_VALUE NUMBER, STATUS VARCHAR2(40 BYTE), ERR_MSG VARCHAR2(400 BYTE), ENTRY_ID NUMBER )

This table will store all information of Sale order item and which price list will be assigned and what rate will be provided to this item. So upload all the price list data in this staging table.

Then you need to create this final procedure to load the price list data from staging table to oracle standard price list base tables.

CREATE OR REPLACE PROCEDURE APPS.xx_plist_proc IS CURSOR plist_info_stg IS SELECT * FROM XXPRICE_LST_TBL WHERE NVL (status, 'N') <> 'P'; v_status VARCHAR2 (1); v_err_msg VARCHAR2 (100); v_item_id NUMBER; v_primary_uom_code VARCHAR2 (3); v_list_header_id NUMBER; BEGIN FOR z1 IN plist_info_stg LOOP v_status:= 'P'; v_err_msg := null; BEGIN SELECT inventory_item_id, primary_uom_code INTO v_item_id, v_primary_uom_code FROM mtl_system_items_b msi WHERE segment1||'.'||segment2||'.'||segment3||'.'||segment4||'.'||segment5 = z1.item_code AND msi.organization_id = z1.org_id; EXCEPTION WHEN NO_DATA_FOUND THEN v_status := 'E'; v_err_msg := 'Inventory Item ID not found'; END; BEGIN SELECT list_header_id INTO v_list_header_id FROM qp_list_headers_tl WHERE NAME = z1.price_list; EXCEPTION WHEN NO_DATA_FOUND THEN v_status := 'E'; v_err_msg := 'Price list ID not found'; END; IF v_item_id IS NOT NULL AND v_primary_uom_code IS NOT NULL AND v_list_header_id IS NOT NULL THEN xx_pricelist (v_list_header_id, to_number(z1.attr_value), v_item_id, v_primary_uom_code ); END IF; UPDATE XXPRICE_LST_TBL SET status= v_status, err_msg = v_err_msg WHERE entry_id = z1.entry_id; COMMIT; END LOOP; END; /

After this just execute this above procedure and all the data will be uploaded in Oracle.