[Home] [Help]
20: )
21: IS
22:
23: /** author: brathod
24: Modified following cursor to refer ap_supplier_sites_all to retrieve supplier address.
25: Cursor was wrongly refering to hz_party_sites_all and hz_locations as
26: jg_zz_vat_trx_details.billing_tp_address_id (value passed in parameter p_party_site_id) is actually a
27: supplier_site_id and not party_site_id. Please refer bug#5031773
28: */
37: , SUBSTR(assa.address_line1,1,35)||' '||
38: SUBSTR(assa.address_line2,1,35)||' '||
39: SUBSTR(assa.address_line3,1,35) ADDRESS_DETAIL
40: , assa.country
41: FROM ap_supplier_sites_all assa
42: , fnd_territories FT
43: WHERE assa.vendor_site_id = p_party_site_id
44: AND assa.country(+) = FT.territory_code;
45:
75: decode(to_char(JZVTD.gl_date,'MM'),'01','1T','02','1T','03','1T','04','2T','05','2T','06','2T','07','3T','08','3T','09','3T','10','4T','11','4T','12','4T') QUARTER_NUM
76:
77: FROM jg_zz_vat_rep_status JZVRS
78: , jg_zz_vat_trx_details JZVTD
79: , AP_SUPPLIER_SITES_ALL APSS, AP_SUPPLIERS APS, ZX_LINES_DET_FACTORS ZXDF ,AP_INVOICES_ALL API
80: WHERE JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
81: AND JZVTD.reporting_status_id in (SELECT DISTINCT JZRS.reporting_status_id JZRS
82: FROM jg_zz_vat_rep_status JZRS
83: WHERE JZRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
123:
124: FROM jg_zz_vat_rep_status JZVRS
125: , jg_zz_vat_trx_details JZVTD
126: , ap_suppliers aps
127: , ap_supplier_sites_all apss
128: , zx_lines_det_factors zxdf
129: , ap_invoices_all api
130:
131: WHERE JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
2519: -- Bug 8485057 added AP_INVOICES_ALL to retrieve the actual current invoice_report_type
2520: lc_jgzz_modelo_query :=
2521: REPLACE( lc_jgzz_modelo_query
2522: , '$MODELO_TABLE_LIST$'
2523: ,', AP_SUPPLIER_SITES_ALL APSS, AP_SUPPLIERS APS, AP_INVOICES_ALL API, ZX_LINES_DET_FACTORS ZXDF ' );
2524:
2525: lc_jgzz_modelo_query :=
2526: REPLACE( lc_jgzz_modelo_query
2527: , '$ADDRESS_ID$'
2661: , NULL YEAR_OF_AMTS_RECEIVED_CASH
2662: FROM jg_zz_vat_rep_status JZVRS
2663: , jg_zz_vat_trx_details JZVTD
2664: , ap_suppliers aps
2665: , ap_supplier_sites_all apss
2666: , ap_invoices_all API
2667: , zx_lines_det_factors zxdf
2668: /**
2669: author:brathod
2811: -- FH Added AP_INVOICES_ALL to table list for Modelo project
2812: lc_jgzz_modelo_query :=
2813: REPLACE( lc_jgzz_modelo_query
2814: , '$MODELO_TABLE_LIST$'
2815: ,', AP_SUPPLIER_SITES_ALL APSS, AP_INVOICES_ALL API, ZX_LINES_DET_FACTORS ZXDF' );
2816:
2817: -- FH: Added Transaction Code for modelo project --
2818: lc_jgzz_modelo_query :=
2819: REPLACE( lc_jgzz_modelo_query
4403: nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt ))) DEDUCTABLE_AMOUNT,
4404: jzvtd.tax_rate TAX_RATE,
4405: APSS.VENDOR_SITE_ID
4406: FROM jg_zz_vat_trx_details jzvtd, jg_zz_vat_rep_status jzvrs
4407: , AP_SUPPLIER_SITES_ALL APSS, AP_SUPPLIERS APS, AP_INVOICES_ALL API,
4408: zx_lines_det_factors zxdf
4409: WHERE (substr(zxdf.trx_business_category,(instr(zxdf.trx_business_category,''MOD'',1,1)+3),3) in (''347'',''349'',''415'')
4410: OR substr(jzvtd.document_sub_type,1,6) = ''MOD340'')
4411: AND nvl(jzvtd.document_sub_type,''X'') <> ''MOD340_EXCL''
4508: nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt ))) DEDUCTABLE_AMOUNT,
4509: jzvtd.tax_rate TAX_RATE,
4510: APSS.VENDOR_SITE_ID
4511: FROM jg_zz_vat_trx_details jzvtd, jg_zz_vat_rep_status jzvrs
4512: , AP_SUPPLIER_SITES_ALL APSS, AP_SUPPLIERS APS, AP_INVOICES_ALL API,
4513: zx_lines_det_factors zxdf
4514: WHERE (substr(zxdf.trx_business_category,(instr(zxdf.trx_business_category,''MOD'',1,1)+3),3) in (''347'',''349'',''415'')
4515: OR substr(jzvtd.document_sub_type,1,6) = ''MOD340'')
4516: AND nvl(jzvtd.document_sub_type,''X'') <> ''MOD340_EXCL''