[Home] [Help]
114: , recs.location_id
115: , (nvl(trx_credit_amount,0) + nvl(trx_debit_amount,0)) repository_tax_amt
116: , recs.organization_type
117: , recs.source_document_id
118: from jai_rgm_trx_refs refs
119: , jai_rgm_trx_records recs
120: where recs.reference_id = refs.reference_id (+)
121: and ( p_organization_id is null
122: or (recs.organization_id = p_organization_id)
151: where location_id = cp_location_id;
152:
153:
154:
155: cursor c_get_settled_doc_service_typ (cp_source jai_rgm_trx_refs.source%type
156: ,cp_invoice_id jai_rgm_trx_refs.invoice_id%type
157: ,cp_line_id jai_rgm_trx_refs.line_id%type
158: )
159: is
152:
153:
154:
155: cursor c_get_settled_doc_service_typ (cp_source jai_rgm_trx_refs.source%type
156: ,cp_invoice_id jai_rgm_trx_refs.invoice_id%type
157: ,cp_line_id jai_rgm_trx_refs.line_id%type
158: )
159: is
160: select recs.service_type_code
153:
154:
155: cursor c_get_settled_doc_service_typ (cp_source jai_rgm_trx_refs.source%type
156: ,cp_invoice_id jai_rgm_trx_refs.invoice_id%type
157: ,cp_line_id jai_rgm_trx_refs.line_id%type
158: )
159: is
160: select recs.service_type_code
161: from jai_rgm_trx_records recs, jai_rgm_trx_refs refs
157: ,cp_line_id jai_rgm_trx_refs.line_id%type
158: )
159: is
160: select recs.service_type_code
161: from jai_rgm_trx_records recs, jai_rgm_trx_refs refs
162: where recs.reference_id = refs.reference_id
163: and refs.invoice_id = cp_invoice_id
164: and refs.line_id = cp_line_id
165: and refs.source = cp_source
246:
247: cursor c_st_transprt_party_details(cp_invoice_id ap_invoices_all.invoice_id%type) /* Changes by nprashar for bug 7172723*/
248: IS
249: select pv.vendor_name,pv.vendor_id from
250: jai_rgm_trx_refs jrtr,
251: po_vendors pv
252: where invoice_id=cp_invoice_id
253: and pv.vendor_id=jrtr.party_id
254: and rownum=1;
1506: , source
1507: , invoice_id
1508: , item_line_id
1509: , line_id /*Added by nprashar for bug # 7172723*/
1510: from jai_rgm_trx_refs refs
1511: where refs.reference_id = p_reference_id;
1512:
1513: /*Addition of code by nprashar for bug # 7172723*/
1514: cursor c_get_source_type
1516: select source
1517: from ap_invoices_all aia
1518: where aia.invoice_id in
1519: (select invoice_id
1520: from jai_rgm_Trx_refs refs
1521: where refs.reference_id = p_reference_id);
1522:
1523: 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)
1524: is
1519: (select invoice_id
1520: from jai_rgm_Trx_refs refs
1521: where refs.reference_id = p_reference_id);
1522:
1523: 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)
1524: is
1525: select inv_dist_id,
1526: line_num
1527: from
1532: where INVOICE_ID=p_invoice_id
1533: )
1534: where inv_dist_id=p_line_id;
1535:
1536: cursor c_get_doc_details(p_invoice_id jai_rgm_trx_refs.invoice_id%type,p_row_number number)
1537: is
1538: SELECT shipment_header_id,
1539: shipment_line_id,
1540: receipt_num,
1909:
1910: /*------------------------------------------------------------------------------------------------------------*/
1911:
1912: procedure derrive_doc_from_ref
1913: ( p_reference_source in jai_rgm_trx_refs.source%type
1914: , p_reference_invoice_id in jai_rgm_trx_refs.invoice_id%type
1915: , p_reference_item_line_id in jai_rgm_trx_refs.item_line_id%type
1916: , p_trx_repo_extract_rec out nocopy jai_trx_repo_extract_gt%rowtype
1917: , p_process_message out nocopy varchar2
1910: /*------------------------------------------------------------------------------------------------------------*/
1911:
1912: procedure derrive_doc_from_ref
1913: ( p_reference_source in jai_rgm_trx_refs.source%type
1914: , p_reference_invoice_id in jai_rgm_trx_refs.invoice_id%type
1915: , p_reference_item_line_id in jai_rgm_trx_refs.item_line_id%type
1916: , p_trx_repo_extract_rec out nocopy jai_trx_repo_extract_gt%rowtype
1917: , p_process_message out nocopy varchar2
1918: , p_process_flag out nocopy varchar2
1911:
1912: procedure derrive_doc_from_ref
1913: ( p_reference_source in jai_rgm_trx_refs.source%type
1914: , p_reference_invoice_id in jai_rgm_trx_refs.invoice_id%type
1915: , p_reference_item_line_id in jai_rgm_trx_refs.item_line_id%type
1916: , p_trx_repo_extract_rec out nocopy jai_trx_repo_extract_gt%rowtype
1917: , p_process_message out nocopy varchar2
1918: , p_process_flag out nocopy varchar2
1919: )
2159: --
2160: -- A wraper function on top of the procedure get_doc_from_reference to return only service type code
2161: -- This functions are used by Serivce Tax reports for printing service type code
2162: --
2163: function get_service_type_from_ref (p_reference_id in jai_rgm_trx_refs.reference_id%type
2164: )
2165:
2166: return varchar2
2167: is
2200: cursor c_get_so_settled_srvtyp
2201: is
2202: select recs.service_type_code
2203: from jai_rgm_trx_records recs
2204: ,jai_rgm_trx_refs refs
2205: ,ra_customer_trx_lines_all ractl
2206: where ractl.interface_line_attribute6 = p_document_line_id
2207: and ractl.interface_line_context = 'ORDER ENTRY'
2208: and ractl.line_type = 'LINE'
2216: cursor c_get_ar_settled_srvtyp
2217: is
2218: select recs.service_type_code
2219: from jai_rgm_trx_records recs
2220: ,jai_rgm_trx_refs refs
2221: where refs.item_line_id = p_document_line_id
2222: and recs.reference_id = refs.reference_id
2223: and recs.settlement_id is not null
2224: and recs.service_type_code is not null
2228: cursor c_get_po_settled_srvtyp
2229: is
2230: select recs.service_type_code
2231: from jai_rgm_trx_records recs
2232: ,jai_rgm_trx_refs refs
2233: ,po_distributions_all pod
2234: ,ap_invoice_distributions_all apd
2235: where pod.po_line_id = p_document_line_id
2236: and pod.po_distribution_id = apd.po_distribution_id
2245: cursor c_get_aps_settled_srvtyp
2246: is
2247: select recs.service_type_code
2248: from jai_rgm_trx_records recs
2249: ,jai_rgm_trx_refs refs
2250: where refs.invoice_id = p_document_id
2251: and refs.item_line_id = p_document_line_id
2252: and recs.reference_id = refs.reference_id
2253: and recs.settlement_id is not null