[Home] [Help]
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
122: 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
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:
1615: /**
1616: author: brathod
1617: Introduced place holder $MODELO_TABLE_LIST$ which can contain additional table required by specific modelo reports
1618: For MODELO=415 and SOURCE=AP value will be AP_SUPPLIER_SITES APSS to add a filter in MODELO_415 AP report
1619: For MODELO=347 and SOURCE=AP value will be AP_SUPPLIERS APS, AP_SUPPLIER_SITES APSS to add filter in MODELO_347 AP report
1620: */
1621:
1622: C_JGZZ_MODELO_GENRIC_QUERY CONSTANT VARCHAR2 (32000) :=
1623: '
2510: --
2511: -- Replace Place Holder with Modelo 347 - AP Specific Values
2512: --
2513: /** author: brathod
2514: $MODELO_TABLE_LIST$ will have value ", AP_SUPPLIER_SITES APSS , AP_SUPPLIERS APS" [please note comma (,)at begining]
2515: as modelo 347 report needs to filter records based on FEDERAL_REPORTABLE_FLAG for suppliers
2516: and TAX_REPORTING_SITE_FLAG for supplier sites
2517:
2518: */
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$'
2624:
2625: lc_jgzz_modelo_query1 :=
2626: 'UNION
2627: /** author: brathod
2628: added two tables AP_SUPPLIER_SITES APSS , AP_SUPPLIERS APS
2629: as modelo 347 report needs to filter records based on FEDERAL_REPORTABLE_FLAG for suppliers
2630: and TAX_REPORTING_SITE_FLAG for supplier sites
2631: */
2632: SELECT /*+ NO_REWRITE */
2660: , NULL TRANSMISSION_PROPERTY_AMT
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: /**
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''