[Home] [Help]
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: */
29: CURSOR c_get_address IS
433: SUBSTR(HL.ADDRESS2,1,23) ||
434: LPAD(SUBSTR(HL.ADDRESS3,1,length(HL.ADDRESS3) -
435: NVL(LENGTH(LTRIM(TRANSLATE(HL.ADDRESS3, '123456789','000000000'),'0')),0)),5,'0') address_detail
436: FROM hz_cust_acct_sites_all HCAS
437: , hz_party_sites HPS
438: , hz_locations HL --12656346
439: , fnd_territories FT
440: , hz_cust_site_uses_all HCSU
441: WHERE HCAS.cust_acct_site_id = p_customer_address_id
478: , SUBSTR(HL.address3||'Z',1,INSTR(HL.address3||'Z',
479: LTRIM(HL.address3||'Z','1234567890')) - 1) numero
480: , HL.country
481: FROM hz_cust_acct_sites_all HCAS
482: , hz_party_sites HPS
483: , hz_locations HL
484: , fnd_territories FT
485: , hz_cust_site_uses_all HCSU
486: WHERE HCAS.cust_acct_site_id = p_customer_address_id
642: 4) property_location_code
643: FROM jg_zz_vat_rep_status JZVRS
644: , jg_zz_vat_trx_details JZVTD
645: , hz_cust_acct_sites_all HCAS
646: , hz_party_sites HPS
647: , hr_locations HL
648: -- , fnd_territories FT -- Bug 8485057: not necessary
649: , hz_cust_site_uses_all HCSU
650: , zx_lines_det_factors ZXDF
1214: )
1215: INTO v_arrenda_amount
1216: FROM JG_ZZ_VAT_TRX_GT JVGT
1217: , hz_cust_acct_sites_all HCAS
1218: , hz_party_sites HPS
1219: , hz_locations HL
1220: , fnd_territories FT
1221: , hz_cust_site_uses_all HCSU
1222: WHERE JVGT.jg_info_n3 = p_customer_id