[Home] [Help]
476: , recs.organization_type
477: , recs.source_document_id
478: , recs.invoice_no --Added by Qiong for Advanced Receipts
479: , recs.source_table_name --Added by Qiong for bug#13456083
480: from jai_rgm_trx_refs refs
481: , jai_rgm_trx_records recs
482: where recs.reference_id = refs.reference_id (+)
483: and ( p_organization_id is null
484: or (recs.organization_id = p_organization_id)
515: is
516: select
517: sum((nvl(credit_amount,0) + nvl(debit_amount,0))) repository_tax_amt -- modified for the bug 14341945
518: from jai_rgm_trx_records recs,
519: jai_rgm_trx_refs refs
520: where recs.reference_id = refs.reference_id (+)
521: and recs.SOURCE_TRX_TYPE = pv_source_trx_type
522: and refs.item_line_id = pv_item_line_id
523: and refs.invoice_id = pn_invoice_id;
530: is
531: select
532: sum((nvl(credit_amount,0) + nvl(debit_amount,0))) repository_tax_amt--Modified by Junjian for bug#14341945 on 2-Aug-2012
533: from jai_rgm_trx_records recs,
534: jai_rgm_trx_refs refs
535: where recs.reference_id = refs.reference_id (+)
536: and recs.SOURCE_TRX_TYPE = pv_source_trx_type
537: and refs.item_line_id = pv_item_line_id
538: and refs.invoice_id = pn_invoice_id
565: where location_id = cp_location_id;
566:
567:
568:
569: cursor c_get_settled_doc_service_typ (cp_source jai_rgm_trx_refs.source%type
570: ,cp_invoice_id jai_rgm_trx_refs.invoice_id%type
571: ,cp_line_id jai_rgm_trx_refs.line_id%type
572: )
573: is
566:
567:
568:
569: cursor c_get_settled_doc_service_typ (cp_source jai_rgm_trx_refs.source%type
570: ,cp_invoice_id jai_rgm_trx_refs.invoice_id%type
571: ,cp_line_id jai_rgm_trx_refs.line_id%type
572: )
573: is
574: select recs.service_type_code
567:
568:
569: cursor c_get_settled_doc_service_typ (cp_source jai_rgm_trx_refs.source%type
570: ,cp_invoice_id jai_rgm_trx_refs.invoice_id%type
571: ,cp_line_id jai_rgm_trx_refs.line_id%type
572: )
573: is
574: select recs.service_type_code
575: from jai_rgm_trx_records recs, jai_rgm_trx_refs refs
571: ,cp_line_id jai_rgm_trx_refs.line_id%type
572: )
573: is
574: select recs.service_type_code
575: from jai_rgm_trx_records recs, jai_rgm_trx_refs refs
576: where recs.reference_id = refs.reference_id
577: and refs.invoice_id = cp_invoice_id
578: and refs.line_id = cp_line_id
579: and refs.source = cp_source
660:
661: cursor c_st_transprt_party_details(cp_invoice_id ap_invoices_all.invoice_id%type) /* Changes by nprashar , Forward porting from bug 7172723*/
662: IS
663: select pv.vendor_name,pv.vendor_id from
664: jai_rgm_trx_refs jrtr,
665: po_vendors pv
666: where invoice_id=cp_invoice_id
667: and pv.vendor_id=jrtr.party_id
668: and rownum=1;
677: lv_organization_name hr_organization_units.name%type;
678: lv_location_name hr_locations_all.description%type;
679: ln_reg_id number;
680: --start additions for bug#10085619
681: cursor c_get_line_number(p_invoice_id jai_rgm_trx_refs.invoice_id%type,p_line_id jai_rgm_trx_refs.line_id%type)
682: is
683: select inv_dist_id,
684: line_num
685: from
1653: ,JAI_AR_TRX_TAX_LINES jrcttl
1654: ,jai_cmn_taxes_all jtc
1655: --Added by Chong.Lei for POT code port begin
1656: , jai_rgm_trx_records jrec --Add by Xiao for POT Phase III, reg bug#12895841.
1657: , jai_rgm_trx_refs jref --Add by Xiao for POT Phase III, reg bug#12895841.
1658: --Added by Chong.Lei for POT code port end
1659: where jrctl.customer_trx_line_id = cp_customer_trx_line_id
1660: and jrcttl.link_to_cust_trx_line_id = jrctl.customer_trx_line_id
1661: and jtc.tax_id = jrcttl.tax_id
1678:
1679: --Added below cursor for bug#9298508 by JMEENA
1680: CURSOR c_get_ra_tax_amt_applied(cp_customer_trx_id NUMBER, cp_cust_trx_line_id NUMBER) IS
1681: SELECT Sum(Nvl(jrec.DEBIT_AMOUNT,0))+Sum(Nvl(jrec.CREDIT_AMOUNT,0)) -- modified for the bug 14341945
1682: FROM jai_rgm_trx_refs jref, jai_rgm_trx_records jrec
1683: WHERE jref.invoice_id= cp_customer_trx_id
1684: AND jref.item_line_id = cp_cust_trx_line_id
1685: AND jrec.source_trx_type = p_source_trx_type--Added by Qinglei on 28-Feb-2012 for bug#13741544
1686: AND jrec.reference_id=jref.reference_id
1802: --------------------------------------------
1803: CURSOR c_get_claim_amount (pv_document_source VARCHAR2) --Add pv_document_source by Xiao for POT Phase III reg bug#12895841. --Modified by Chong.Lei for POT code port
1804: IS
1805: SELECT abs(SUM(NVL(jrec.DEBIT_AMOUNT,0))-SUM(NVL(jrec.CREDIT_AMOUNT,0))) --Modified by Qiong for reverse charge bug#16001407
1806: FROM jai_rgm_trx_refs jref,
1807: jai_rgm_trx_records jrec
1808: WHERE jref.invoice_id = p_document_id
1809: AND jref.item_line_id = p_document_line_id
1810: AND jrec.source_document_id = p_taxline_no
1873:
1874: CURSOR c_get_claim_paid_amount IS
1875: SELECT SUM(aip.amount)
1876: FROM ap_invoice_payments_all aip
1877: WHERE EXISTS (SELECT 1 FROM jai_rgm_trx_refs jref,
1878: jai_rgm_trx_records jrec
1879: WHERE jref.invoice_id = p_document_id
1880: AND jref.item_line_id = p_document_line_id
1881: AND jrec.reference_id = jref.reference_id
1888: CURSOR c_get_repo_amount (pv_document_source VARCHAR2) --Add pv_document_source by Xiao for POT Phase III reg bug#12895841. --Modified by Chong.Lei for POT code port
1889: IS
1890: SELECT abs(SUM(NVL(jrec.DEBIT_AMOUNT,0))-SUM(NVL(jrec.CREDIT_AMOUNT,0)))--add abs, change '+' to '-' by Xiao for POT, reg bug#12533434
1891: --Modified by Qiong for reverse charge bug#16001407
1892: FROM jai_rgm_trx_refs jref,
1893: jai_rgm_trx_records jrec
1894: WHERE jref.invoice_id = p_document_id
1895: AND jref.item_line_id = p_document_line_id --Add by Xiao fro POT Phase III, reg bug#12895841. --Modified by Chong.Lei for POT code port
1896: AND jrec.reference_id = jref.reference_id
1964: AND araa.receivable_application_id = p_document_id;
1965:
1966: CURSOR get_rcpt_amt_cur IS
1967: SELECT SUM(nvl(jrec.trx_debit_amount,0)) + SUM(nvl(jrec.trx_credit_amount,0))
1968: FROM jai_rgm_trx_refs jref,
1969: jai_rgm_trx_records jrec
1970: WHERE jrec.source_document_id = p_document_id
1971: AND jref.reference_id = jrec.reference_id
1972: AND jrec.source_table_name = 'AR_CASH_RECEIPTS_ALL'
1975: AND jrec.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS');
1976:
1977: CURSOR get_rcpt_app_amt_cur IS
1978: SELECT SUM(nvl(jrec.trx_debit_amount,0)) + SUM(nvl(jrec.trx_credit_amount,0))
1979: FROM jai_rgm_trx_refs jref,
1980: jai_rgm_trx_records jrec
1981: WHERE jrec.source_document_id = p_document_id
1982: AND jref.reference_id = jrec.reference_id
1983: AND jrec.source_table_name = 'AR_RECEIVABLE_APPLICATIONS_ALL'
2059: ------------------------------------------------------------------------------------
2060: --Add by Xiao for POT Phase III changes, reg bug#12895841.
2061: CURSOR get_claim_amt_cur IS
2062: SELECT SUM(nvl(jrec.debit_amount,0)) + SUM(nvl(jrec.credit_amount,0)) --Modified by Qiong for reverse charge bug#16001407
2063: FROM jai_rgm_trx_refs jref,
2064: jai_rgm_trx_records jrec
2065: WHERE jref.invoice_id = p_document_id
2066: AND jref.reference_id = jrec.reference_id
2067: AND jref.item_line_id = p_document_line_id
2898: , source
2899: , invoice_id
2900: , item_line_id
2901: , line_id /*Added by nprashar for bug # 7172723*/
2902: from jai_rgm_trx_refs refs
2903: where refs.reference_id = p_reference_id;
2904:
2905: /*Addition of code by nprashar for bug # 7172723*/
2906: cursor c_get_source_type
2908: select source
2909: from ap_invoices_all aia
2910: where aia.invoice_id in
2911: (select invoice_id
2912: from jai_rgm_Trx_refs refs
2913: where refs.reference_id = p_reference_id);
2914:
2915: cursor c_get_line_number(p_invoice_id jai_rgm_trx_refs.invoice_id%type,p_line_id jai_rgm_trx_refs.line_id%type)
2916: is
2911: (select invoice_id
2912: from jai_rgm_Trx_refs refs
2913: where refs.reference_id = p_reference_id);
2914:
2915: cursor c_get_line_number(p_invoice_id jai_rgm_trx_refs.invoice_id%type,p_line_id jai_rgm_trx_refs.line_id%type)
2916: is
2917: select inv_dist_id,
2918: line_num
2919: from
2927:
2928: lr_line_number number;
2929: lr_inv_dist_id ap_invoice_distributions_all.INVOICE_DISTRIBUTION_ID%type;
2930:
2931: cursor c_get_doc_details(p_invoice_id jai_rgm_trx_refs.invoice_id%type,p_row_number number)
2932: is
2933: SELECT shipment_header_id,
2934: shipment_line_id,
2935: receipt_num,
3324:
3325: /*------------------------------------------------------------------------------------------------------------*/
3326:
3327: procedure derrive_doc_from_ref
3328: ( p_reference_source in jai_rgm_trx_refs.source%type
3329: , p_source_trx_type in jai_rgm_trx_records.source_trx_type%type default NULL
3330: , p_reference_invoice_id in jai_rgm_trx_refs.invoice_id%type
3331: , p_reference_item_line_id in jai_rgm_trx_refs.item_line_id%type
3332: , p_trx_repo_extract_rec out nocopy jai_trx_repo_extract_gt%rowtype
3326:
3327: procedure derrive_doc_from_ref
3328: ( p_reference_source in jai_rgm_trx_refs.source%type
3329: , p_source_trx_type in jai_rgm_trx_records.source_trx_type%type default NULL
3330: , p_reference_invoice_id in jai_rgm_trx_refs.invoice_id%type
3331: , p_reference_item_line_id in jai_rgm_trx_refs.item_line_id%type
3332: , p_trx_repo_extract_rec out nocopy jai_trx_repo_extract_gt%rowtype
3333: , p_process_message out nocopy varchar2
3334: , p_process_flag out nocopy varchar2
3327: procedure derrive_doc_from_ref
3328: ( p_reference_source in jai_rgm_trx_refs.source%type
3329: , p_source_trx_type in jai_rgm_trx_records.source_trx_type%type default NULL
3330: , p_reference_invoice_id in jai_rgm_trx_refs.invoice_id%type
3331: , p_reference_item_line_id in jai_rgm_trx_refs.item_line_id%type
3332: , p_trx_repo_extract_rec out nocopy jai_trx_repo_extract_gt%rowtype
3333: , p_process_message out nocopy varchar2
3334: , p_process_flag out nocopy varchar2
3335: )
3565: --
3566: -- A wraper function on top of the procedure get_doc_from_reference to return only service type code
3567: -- This functions are used by Serivce Tax reports for printing service type code
3568: --
3569: function get_service_type_from_ref (p_reference_id in jai_rgm_trx_refs.reference_id%type
3570: )
3571:
3572: return varchar2
3573: is
3606: cursor c_get_so_settled_srvtyp
3607: is
3608: select recs.service_type_code
3609: from jai_rgm_trx_records recs
3610: ,jai_rgm_trx_refs refs
3611: ,ra_customer_trx_lines_all ractl
3612: where ractl.interface_line_attribute6 = p_document_line_id
3613: and ractl.interface_line_context = 'ORDER ENTRY'
3614: and ractl.line_type = 'LINE'
3622: cursor c_get_ar_settled_srvtyp
3623: is
3624: select recs.service_type_code
3625: from jai_rgm_trx_records recs
3626: ,jai_rgm_trx_refs refs
3627: where refs.item_line_id = p_document_line_id
3628: and recs.reference_id = refs.reference_id
3629: and recs.settlement_id is not null
3630: and recs.service_type_code is not null
3634: cursor c_get_po_settled_srvtyp
3635: is
3636: select recs.service_type_code
3637: from jai_rgm_trx_records recs
3638: ,jai_rgm_trx_refs refs
3639: ,po_distributions_all pod
3640: ,ap_invoice_distributions_all apd
3641: where pod.po_line_id = p_document_line_id
3642: and pod.po_distribution_id = apd.po_distribution_id