Thursday 14 July 2016

/*Sample script to fetch the Receipt Register along with its TDS and WCT details */

SELECT   apps.gl_flexfields_pkg.get_description_sql
                                            (cc.chart_of_accounts_id,
                                             1,
                                             cc.segment1
                                            ) company,
                  apps.gl_flexfields_pkg.get_description_sql
                                      (cc.chart_of_accounts_id,
                                       2,
                                       cc.segment2
                                      ) business_unit,
                  apps.gl_flexfields_pkg.get_description_sql
                                             (cc.chart_of_accounts_id,
                                              4,
                                              cc.segment4
                                             ) region,
                  apps.gl_flexfields_pkg.get_description_sql
                                           (cc.chart_of_accounts_id,
                                            5,
                                            cc.segment5
                                           ) LOCATION,
                  NVL (acr.doc_sequence_value, '0') document_no,
                  acr.receipt_date, acr.receipt_number receipt_no,
                  NVL (acr.customer_receipt_reference, ' ') chq_num,
                  acr.postmark_date chq_date,
                  rat.TRX_NUMBER invoice_no,
                  rat.TRX_DATE invoice_date,
                  rc.customer_number customer_code,
                  rc.customer_name customer_name,
                  remit_bank_branch.bank_name remittance_bank_name,
                  acr.amount receipt_amount, acr.cash_receipt_id,
                  acr.currency_code,
                  DECODE
                     (acr.TYPE,
                      'MISC', NULL,
                      NVL (SUM (DECODE (arr.status,
                                        'APP', NVL (arr.amount_applied, 0),
                                        0
                                       )
                               ),
                           0
                          )
                     ) applied_amount,
                  DECODE
                     (acr.TYPE,
                      'MISC', NULL,
                      NVL (SUM (DECODE (arr.status,
                                        'UNAPP', NVL (arr.amount_applied, 0),
                                        0
                                       )
                               ),
                           0
                          )
                     ) unnapplied_amount,
                  DECODE
                     (acr.TYPE,
                      'MISC', NULL,
                      NVL (SUM (DECODE (arr.status,
                                        'ACC', NVL (arr.amount_applied, 0),
                                        0
                                       )
                               ),
                           0
                          )
                     ) on_account_amount,
                  DECODE
                     (acr.TYPE,
                      'MISC', NULL,
                      NVL (SUM (DECODE (arr.status,
                                        'UNID', NVL (arr.amount_applied, 0),
                                        0
                                       )
                               ),
                           0
                          )
                     ) unidentified_amount,
                  SUM (arr.attribute1) "TDS",
                  SUM (arr.attribute2) "WCT",
                  arr.attribute3 "OTHER",
                  rc.customer_id, rat.customer_trx_id
             FROM apps.hz_cust_accounts_all cust_acct,
                  apps.ar_payment_schedules_all ps,
                  apps.ar_receivable_applications_all arr,
                  apps.hz_cust_acct_sites_all acct_site,
                  apps.hz_party_sites party_site,
                  apps.hz_locations loc,
                  apps.hz_cust_site_uses_all site_uses,
                  apps.ar_cash_receipts_all acr,
                  apps.ar_cash_receipt_history_all crh,
                  apps.gl_code_combinations cc,
                  apps.ra_customer_trx_all rat,
                  apps.ra_customers rc,
                  apps.ap_bank_accounts_all remit_bank,
                  apps.ap_bank_branches remit_bank_branch
            WHERE ps.customer_id = cust_acct.cust_account_id
              AND ps.customer_id = cust_acct.cust_account_id
              AND acct_site.party_site_id = party_site.party_site_id
              AND loc.location_id = party_site.location_id
              AND ps.cash_receipt_id = acr.cash_receipt_id
              AND acr.cash_receipt_id = crh.cash_receipt_id
              AND crh.account_code_combination_id = cc.code_combination_id
              AND ps.CLASS = 'PMT'
              AND ps.cash_receipt_id = arr.cash_receipt_id
              --AND ps.status = 'OP'
              AND site_uses.site_use_code = 'BILL_TO'
              AND site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
              AND NVL (site_uses.status, 'A') = 'A'
              AND cust_acct.cust_account_id = acct_site.cust_account_id
              AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
              AND ps.customer_id = acct_site.cust_account_id
              AND ps.customer_site_use_id = site_uses.site_use_id
              --AND arr.applied_customer_trx_id = rat.customer_trx_id(+)
              AND rat.customer_trx_id(+) = ps.customer_trx_id
              AND acr.pay_from_customer = rc.customer_id
              AND remit_bank.bank_account_id(+) =
                                                acr.remittance_bank_account_id
              AND remit_bank.bank_branch_id = remit_bank_branch.bank_branch_id(+)
              AND crh.cash_receipt_history_id = arr.cash_receipt_history_id
              -- AND crh.first_posted_record_flag = 'Y'
              AND NVL (acr.confirmed_flag, 'Y') = 'Y'
              AND NVL (arr.confirmed_flag, 'Y') = 'Y'
              AND TRUNC (acr.receipt_date)
                     BETWEEN TO_DATE (NVL (p_from_date,
                                           TO_CHAR (SYSDATE,
                                                    'RRRR/MM/DD hh24:mi:ss'
                                                   )
                                          ),
                                      'RRRR/MM/DD hh24:mi:ss'
                                     )
                         AND TO_DATE (NVL (p_to_date,
                                           TO_CHAR (SYSDATE,
                                                    'RRRR/MM/DD hh24:mi:ss'
                                                   )
                                          ),
                                      'RRRR/MM/DD hh24:mi:ss'
                                     )
            
              AND rc.customer_id = NVL (p_customer, rc.customer_id)
         
         GROUP BY acr.TYPE,
                  apps.gl_flexfields_pkg.get_description_sql
                                                     (cc.chart_of_accounts_id,
                                                      1,
                                                      cc.segment1
                                                     ),
                  apps.gl_flexfields_pkg.get_description_sql
                                                     (cc.chart_of_accounts_id,
                                                      2,
                                                      cc.segment2
                                                     ),
                  apps.gl_flexfields_pkg.get_description_sql
                                                     (cc.chart_of_accounts_id,
                                                      4,
                                                      cc.segment4
                                                     ),
                  apps.gl_flexfields_pkg.get_description_sql
                                                     (cc.chart_of_accounts_id,
                                                      5,
                                                      cc.segment5
                                                     ),
                  NVL (acr.doc_sequence_value, '0'),
                  acr.receipt_date,
                  acr.receipt_number,
                  NVL (acr.customer_receipt_reference, ' '),
                  acr.postmark_date,
                  acr.cash_receipt_id,
                  rat.trx_date,
                  rat.ct_reference,
                  acr.amount,                                  --vat.tax_code,
                  acr.cash_receipt_id,
                  acr.currency_code,
                  rc.customer_number,
                  rc.customer_name,
                  remit_bank_branch.bank_name,
                  rat.created_by,
                  rc.customer_id,
                  rat.customer_trx_id,
                  arr.attribute3
         ORDER BY acr.receipt_number;

By

Deepak J

No comments:

Post a Comment