Israr Oracle: Customer Data with Bank Report

How to find the customer data with bank details

CREATE OR REPLACE PROCEDURE APPS.XXPCL_CUST_DETAILS_PROC(
RETCODE OUT VARCHAR2,
ERRBUF OUT VARCHAR2,
P_ORG_ID IN VARCHAR2,
P_PARTY_ID IN VARCHAR2,
P_PARTY_NUMBER IN VARCHAR2
--P1_FROM_DATE IN VARCHAR2,
--P1_TO_DATE IN VARCHAR2
)
AS

--P_FROM_DATE DATE;
--P_TO_DATE DATE;
P_V_COUNT NUMBER;

--*==================================================+
--| PACKAGE XXPCL_CUST_DETAILS_PROC
--+--------------------------------------------------------------------------+
--| DESCRIPTION This package is used to designed for xml for L trial
--|
--+--------------------------------------------------------------------------+
--| HISTORY
--| Author | Date | DESCRIPTION
--+--------------------------------------------------------------------------+
--| Technical Israr| 16-AUG-2018| 1.0
-- Technical.israr@gmail.com

--+==================================================*/

--- Customer information with email and mobile no.--------
--CURSOR C1 IS
--
-- SELECT DISTINCT HP.PARTY_ID,
-- HP.PARTY_NAME CUSTOMER_NAME,
-- HP.PARTY_NUMBER,
-- HCA.CUST_ACCOUNT_ID,
-- HCA.ACCOUNT_NUMBER,
-- NAME ORG_NAME,
-- HCAS.CUST_ACCT_SITE_ID,
-- HPS.PARTY_SITE_NUMBER,
-- HCSU.SITE_USE_CODE,
-- HL.ADDRESS1,
-- HL.ADDRESS2,
-- HL.ADDRESS3,
-- HL.ADDRESS4,
-- HL.CITY,
-- HL.POSTAL_CODE,
-- HL.STATE,
-- HL.PROVINCE,
-- HL.COUNTY,
-- HL.COUNTRY,
-- HL.ADDRESS_STYLE,
-- APPS.XXPCL_CON_EMAIL_MO (1, HPS.PARTY_SITE_ID) EMAIL,
-- APPS.XXPCL_CON_EMAIL_MO (2, HPS.PARTY_SITE_ID) PHONE
-- FROM APPS.HZ_PARTIES HP,
-- APPS.HZ_PARTY_SITES HPS,
-- APPS.HZ_CUST_ACCOUNTS_ALL HCA,
-- APPS.HZ_CUST_ACCT_SITES_ALL HCAS,
-- APPS.HZ_CUST_SITE_USES_ALL HCSU,
-- APPS.HZ_LOCATIONS HL,
-- APPS.HR_OPERATING_UNITS HR
-- WHERE 1 = 1
-- AND HCAS.ORG_ID = HR.ORGANIZATION_ID
-- AND SET_OF_BOOKS_ID = 2121
-- AND HP.PARTY_ID = HCA.PARTY_ID
-- AND HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID(+)
-- AND HPS.PARTY_SITE_ID(+) = HCAS.PARTY_SITE_ID
-- AND HCAS.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID
-- AND HPS.LOCATION_ID = HL.LOCATION_ID(+)
-- AND HP.STATUS = 'A'
-- AND HCAS.STATUS = 'A'
-- AND HCAS.ORG_ID = NVL (P_ORG_ID, HCAS.ORG_ID)
-- AND HP.PARTY_ID = NVL (P_PARTY_ID, HP.PARTY_ID)
-- AND HP.PARTY_NUMBER =NVL(P_PARTY_NUMBER,HP.PARTY_NUMBER)
--ORDER BY HP.PARTY_NAME;

CURSOR C2 IS
SELECT DISTINCT HP.PARTY_ID,HPS.PARTY_SITE_ID,
HP.PARTY_NAME "CUSTOMER_NAME",
HP.PARTY_NUMBER CUSTOMER_NUM,
NAME ORG_NAME,
HCAS.ATTRIBUTE1 GST_NUM,
PAN_NO,
HL.ADDRESS1, HL.ADDRESS2, HL.ADDRESS3, HL.ADDRESS4, HL.CITY, HL.POSTAL_CODE, HL.STATE,
--HCP1.PHONE_NUMBER,HCP.EMAIL_ADDRESS,
XXPCL_CON_EMAIL_MO(1,HPS.PARTY_SITE_ID) EMAIL_ADDRESS,
XXPCL_CON_EMAIL_MO(2,HPS.PARTY_SITE_ID) PHONE_NUMBER,
IEBA.BANK_NAME,
IEBA.BANK_ACCOUNT_NUMBER,
IEBA.BRANCH_NUMBER,IEBA.BANK_BRANCH_NAME,IEBA.BANK_NUMBER,IEBA.DESCRIPTION
FROM HZ_PARTIES HP,
HZ_PARTY_SITES HPS,
HZ_CUST_ACCOUNTS_ALL HCA,
HZ_CUST_ACCT_SITES_ALL HCAS,
HZ_CUST_SITE_USES_ALL HCSU,
HZ_LOCATIONS HL,
JAI_CMN_CUS_ADDRESSES JCCA,
HR_OPERATING_UNITS HR,
APPS.IBY_PAYEE_ASSIGNED_BANKACCT_V IPAB,
IBY_ACCOUNT_OWNERS IAO,
IBY_EXT_BANK_ACCOUNTS_V IEBA
WHERE 1 = 1
AND HP.PARTY_ID = HCA.PARTY_ID
AND JCCA.CUSTOMER_ID(+)= HCAS.CUST_ACCOUNT_ID
AND HR.SET_OF_BOOKS_ID = 2121
AND HR.ORGANIZATION_ID = HCAS.ORG_ID
AND HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID(+)
AND HPS.PARTY_SITE_ID = HCAS.PARTY_SITE_ID
AND HCAS.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID
AND HP.STATUS = 'A'
AND HPS.STATUS = 'A'
AND HPS.LOCATION_ID = HL.LOCATION_ID(+)
AND HP.PARTY_ID=IAO.ACCOUNT_OWNER_PARTY_ID(+)
AND HPS.PARTY_SITE_ID=IPAB.PARTY_SITE_ID(+)
AND IPAB.PARTY_SITE_ID IS NULL
--AND IPAB.EXT_BANK_ACCOUNT_ID = IEBA.EXT_BANK_ACCOUNT_ID
AND IAO.EXT_BANK_ACCOUNT_ID=IEBA.EXT_BANK_ACCOUNT_ID(+)
AND IEBA.BANK_NUMBER(+)='PFC BANK'
AND IEBA.end_date is null
AND IAO.END_DATE IS NULL
AND HCAS.ORG_ID = NVL (P_ORG_ID, HCAS.ORG_ID)
AND HP.PARTY_ID = NVL ( P_PARTY_ID, HP.PARTY_ID) --445064,494271
AND HP.PARTY_NUMBER =NVL(P_PARTY_NUMBER,HP.PARTY_NUMBER)
--AND HP.PARTY_ID IN(4705 ,497248)
-- ORDER BY HP.PARTY_NAME;
UNION
SELECT DISTINCT HP.PARTY_ID,HPS.PARTY_SITE_ID,
HP.PARTY_NAME "CUSTOMER_NAME",
HP.PARTY_NUMBER CUSTOMER_NUM,
NAME ORG_NAME,
HCAS.ATTRIBUTE1 GST_NUM,
PAN_NO,
HL.ADDRESS1, HL.ADDRESS2, HL.ADDRESS3, HL.ADDRESS4, HL.CITY, HL.POSTAL_CODE, HL.STATE,
--HCP1.PHONE_NUMBER,HCP.EMAIL_ADDRESS,
XXPCL_CON_EMAIL_MO(1,HPS.PARTY_SITE_ID) EMAIL_ADDRESS,
XXPCL_CON_EMAIL_MO(2,HPS.PARTY_SITE_ID) PHONE_NUMBER,
NULL,-- IEBA.BANK_NAME,
NULL,--IEBA.BANK_ACCOUNT_NUMBER,
-- IEBA.BRANCH_NUMBER,IEBA.BANK_BRANCH_NAME,IEBA.BANK_NUMBER,IEBA.DESCRIPTION
NULL,NULL,NULL,NULL
FROM HZ_PARTIES HP,
HZ_PARTY_SITES HPS,
HZ_CUST_ACCOUNTS_ALL HCA,
HZ_CUST_ACCT_SITES_ALL HCAS,
HZ_CUST_SITE_USES_ALL HCSU,
HZ_LOCATIONS HL,
JAI_CMN_CUS_ADDRESSES JCCA,
HR_OPERATING_UNITS HR,
IBY_ACCOUNT_OWNERS IAO,
IBY_EXT_BANK_ACCOUNTS_V IEBA
WHERE 1 = 1
AND HP.PARTY_ID = HCA.PARTY_ID
AND JCCA.CUSTOMER_ID(+)= HCAS.CUST_ACCOUNT_ID
AND HR.SET_OF_BOOKS_ID = 2121
AND HR.ORGANIZATION_ID = HCAS.ORG_ID
AND HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID(+)
AND HPS.PARTY_SITE_ID = HCAS.PARTY_SITE_ID
AND HCAS.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID
AND HP.STATUS = 'A'
AND HPS.STATUS = 'A'
AND HPS.LOCATION_ID = HL.LOCATION_ID(+)
AND HP.PARTY_ID=IAO.ACCOUNT_OWNER_PARTY_ID(+)
-- AND HPS.PARTY_SITE_ID=IPAB.PARTY_SITE_ID(+)
-- and IPAB.PARTY_SITE_ID is null
--AND IPAB.EXT_BANK_ACCOUNT_ID = IEBA.EXT_BANK_ACCOUNT_ID
AND EXISTS (Select 1 from APPS.IBY_PAYEE_ASSIGNED_BANKACCT_V IPAB WHERE IPAB.PARTY_SITE_ID(+)=HPS.PARTY_SITE_ID)
AND IAO.EXT_BANK_ACCOUNT_ID=IEBA.EXT_BANK_ACCOUNT_ID(+)
AND IEBA.BANK_NUMBER(+)='PFC BANK'
AND IEBA.end_date is null
AND HCAS.ORG_ID = NVL (P_ORG_ID, HCAS.ORG_ID)
AND HP.PARTY_ID = NVL ( P_PARTY_ID, HP.PARTY_ID)
AND HP.PARTY_NUMBER =NVL(P_PARTY_NUMBER,HP.PARTY_NUMBER)
-- AND HP.PARTY_ID IN(4705 ,497248)
ORDER BY 3;

--SELECT DISTINCT HP.PARTY_ID,HPS.PARTY_SITE_ID,
-- HP.PARTY_NAME "CUSTOMER_NAME",
-- HP.PARTY_NUMBER CUSTOMER_NUM,
-- NAME ORG_NAME,
-- HCAS.ATTRIBUTE1 GST_NUM,
-- PAN_NO,
-- HL.ADDRESS1, HL.ADDRESS2, HL.ADDRESS3, HL.ADDRESS4, HL.CITY, HL.POSTAL_CODE, HL.STATE,HCP1.PHONE_NUMBER,HCP.EMAIL_ADDRESS,
-- IEBA.BANK_NAME,
-- IEBA.BANK_ACCOUNT_NUMBER,
-- IEBA.BRANCH_NUMBER,IEBA.BANK_BRANCH_NAME,IEBA.BANK_NUMBER,IEBA.DESCRIPTION
-- FROM HZ_PARTIES HP,
-- HZ_PARTY_SITES HPS,
-- HZ_CUST_ACCOUNTS_ALL HCA,
-- HZ_CUST_ACCT_SITES_ALL HCAS,
-- HZ_CUST_SITE_USES_ALL HCSU,
-- HZ_LOCATIONS HL,
-- HZ_CONTACT_POINTS HCP,
-- HZ_CONTACT_POINTS HCP1,
-- JAI_CMN_CUS_ADDRESSES JCCA,
-- HR_OPERATING_UNITS HR,
-- IBY_ACCOUNT_OWNERS IAO,
-- IBY_EXT_BANK_ACCOUNTS_V IEBA
-- WHERE 1 = 1
-- AND HP.PARTY_ID = HCA.PARTY_ID
-- AND JCCA.CUSTOMER_ID(+)= HCAS.CUST_ACCOUNT_ID
-- AND HR.SET_OF_BOOKS_ID = 2121
-- AND HR.ORGANIZATION_ID = HCAS.ORG_ID
-- AND HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID(+)
-- AND HPS.PARTY_SITE_ID = HCAS.PARTY_SITE_ID
-- AND HCAS.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID
-- AND HP.STATUS = 'A'
-- AND HPS.STATUS = 'A'
-- AND HCAS.STATUS = 'A'
-- AND HCP.STATUS='A'
-- AND HCP1.STATUS='A'
-- AND HCP.OWNER_TABLE_ID(+)=HPS.PARTY_SITE_ID
-- AND HCP.CONTACT_POINT_TYPE(+)='EMAIL'
-- AND HCP1.OWNER_TABLE_ID(+)=HPS.PARTY_SITE_ID
-- AND HCP1.CONTACT_POINT_TYPE(+)='PHONE'
-- AND HPS.LOCATION_ID = HL.LOCATION_ID(+)
-- AND HP.PARTY_ID=IAO.ACCOUNT_OWNER_PARTY_ID(+)
-- AND IAO.EXT_BANK_ACCOUNT_ID=IEBA.EXT_BANK_ACCOUNT_ID(+)
-- AND BANK_NUMBER(+)='PFC BANK'
-- AND HCAS.ORG_ID = NVL (P_ORG_ID, HCAS.ORG_ID)
-- AND HP.PARTY_ID = NVL (P_PARTY_ID, HP.PARTY_ID)
-- AND HP.PARTY_NUMBER =NVL(P_PARTY_NUMBER,HP.PARTY_NUMBER)
-- ORDER BY HP.PARTY_NAME;

BEGIN
-- select FND_DATE.CANONICAL_TO_DATE(P1_FROM_DATE), FND_DATE.CANONICAL_TO_DATE(P1_TO_DATE) INTO P_FROM_DATE, P_TO_DATE from dual;
--
-- fnd_file.put_line (fnd_file.log,'P1_FROM_DATE :- '||P1_FROM_DATE);
-- fnd_file.put_line (fnd_file.log,'P1_TO_DATE :- '||P1_TO_DATE);
-- fnd_file.put_line (fnd_file.log,'AS_ON_DATE:- '||P_FROM_DATE);
-- fnd_file.put_line (fnd_file.log,'AS_ON_DATE:- '||P_TO_DATE);
--
fnd_file.put_line (fnd_file.output, '<?xml version="1.0"?>');
fnd_file.put_line (fnd_file.output, '<PCL_VD_DT>');
-- fnd_file.put_line (fnd_file.output,'<p_from_date>' || p_from_date || '</p_from_date>' );
-- fnd_file.put_line (fnd_file.output,'<p_to_date>' || p_to_date || '</p_to_date>' );

FOR I IN C2 LOOP
fnd_file.put_line (fnd_file.output, '<VD_HEADER>');
fnd_file.put_line (fnd_file.output,'<PARTY_ID>' || I.PARTY_ID || '</PARTY_ID>' );
fnd_file.put_line (fnd_file.output,'<PARTY_SITE_ID>' || I.PARTY_SITE_ID || '</PARTY_SITE_ID>' );

fnd_file.put_line (fnd_file.output,'<CUSTOMER_NAME>' || trim(REPLACE (I.CUSTOMER_NAME, '&', '&' || 'amp;')) || '</CUSTOMER_NAME>' );
fnd_file.put_line (fnd_file.output,'<CUSTOMER_NUM>' || I.CUSTOMER_NUM || '</CUSTOMER_NUM>' );
fnd_file.put_line (fnd_file.output,'<ORG_NAME>' || trim(REPLACE (I.ORG_NAME, '&', '&' || 'amp;')) || '</ORG_NAME>' );

fnd_file.put_line (fnd_file.output,'<GST_NUM>' || trim(REPLACE (I.GST_NUM, '&', '&' || 'amp;')) || '</GST_NUM>' );
fnd_file.put_line (fnd_file.output,'<PAN_NO>' || I.PAN_NO || '</PAN_NO>' );
fnd_file.put_line (fnd_file.output,'<ADDRESS1>' || trim(REPLACE (I.ADDRESS1, '&', '&' || 'amp;')) || '</ADDRESS1>' );
fnd_file.put_line (fnd_file.output,'<ADDRESS2>' || trim(REPLACE (I.ADDRESS2, '&', '&' || 'amp;')) || '</ADDRESS2>' );
fnd_file.put_line (fnd_file.output,'<ADDRESS3>' || trim(REPLACE (I.ADDRESS3, '&', '&' || 'amp;')) || '</ADDRESS3>' );
fnd_file.put_line (fnd_file.output,'<POSTAL_CODE>' || I.POSTAL_CODE || '</POSTAL_CODE>' );

fnd_file.put_line (fnd_file.output,'<CITY>' || trim(REPLACE (I.CITY, '&', '&' || 'amp;')) || '</CITY>' );
fnd_file.put_line (fnd_file.output,'<STATE>' || trim(REPLACE (I.STATE, '&', '&' || 'amp;')) || '</STATE>' );

fnd_file.put_line (fnd_file.output,'<PHONE_NUMBER>' || I.PHONE_NUMBER || '</PHONE_NUMBER>' );
fnd_file.put_line (fnd_file.output,'<EMAIL_ADDRESS>' || trim(REPLACE (I.EMAIL_ADDRESS, '&', '&' || 'amp;')) || '</EMAIL_ADDRESS>' );

--BANK DETAILS
fnd_file.put_line (fnd_file.output,'<BANK_NAME>' || trim(REPLACE (I.BANK_NAME, '&', '&' || 'amp;')) || '</BANK_NAME>' );
fnd_file.put_line (fnd_file.output,'<BANK_ACCOUNT_NUMBER>' || trim(REPLACE (I.BANK_ACCOUNT_NUMBER, '&', '&' || 'amp;')) || '</BANK_ACCOUNT_NUMBER>' );
fnd_file.put_line (fnd_file.output,'<BRANCH_NUMBER>' || trim(REPLACE (I.BRANCH_NUMBER, '&', '&' || 'amp;')) || '</BRANCH_NUMBER>' );
fnd_file.put_line (fnd_file.output,'<BANK_BRANCH_NAME>' || trim(REPLACE (I.BANK_BRANCH_NAME, '&', '&' || 'amp;')) || '</BANK_BRANCH_NAME>' );
fnd_file.put_line (fnd_file.output,'<BANK_NUMBER>' || I.BANK_NUMBER || '</BANK_NUMBER>' );
fnd_file.put_line (fnd_file.output,'<DESCRIPTION>' || trim(REPLACE (I.DESCRIPTION, '&', '&' || 'amp;')) || '</DESCRIPTION>' );

P_V_COUNT:=1;
fnd_file.put_line (fnd_file.output, '</VD_HEADER>');
END LOOP;
fnd_file.put_line (fnd_file.output, '</PCL_VD_DT>');
EXCEPTION WHEN OTHERS THEN fnd_file.put_line (fnd_file.LOG, 'Entered INTO exception' || SQLCODE || SQLERRM );

END;

/

----------------------------------------

------------------------------------------Function details------------------------

Note :- Function name :-----XXPCL_CON_EMAIL_MO

CREATE OR REPLACE FUNCTION APPS.XXPCL_CON_EMAIL_MO (P_NO_ID NUMBER ,P_PARTY_SITE_ID NUMBER) RETURN VARCHAR2
AS

-- FUNCTION NAME --XXPCL_CON_EMAIL_MO
--Techical Israr
--Technical.israr@gmail.com 05-oct-2018

V_EMAIL_ADDRESS VARCHAR2(100);
V_PHONE_NUMBER VARCHAR2(100);

V_COUNT NUMBER := 1;

CURSOR C1 IS
SELECT hcp.email_address,HPS.PARTY_SITE_ID
from hz_parties hz,
hz_party_sites hps,
hz_contact_points hcp
where 1=1
and hz.party_id=hps.party_id
and hps.party_site_id=hcp.owner_table_id
and HCP.status='A'
AND CONTACT_POINT_TYPE='EMAIL'
AND hps.party_site_id=P_PARTY_SITE_ID;
--and hz.party_id=494220

CURSOR C2
IS
SELECT A.PHONE_NUMBER
FROM HZ_CONTACT_POINTS A
WHERE 1 = 1 AND A.status = 'A' AND A.CONTACT_POINT_TYPE = 'PHONE' AND A.OWNER_TABLE_ID=P_PARTY_SITE_ID;

BEGIN

FOR I IN C1 LOOP

IF V_COUNT = 1 THEN

V_EMAIL_ADDRESS:= I.EMAIL_ADDRESS;
-- V_PHONE_NUMBER:=I.PHONE_NUMBER;

V_COUNT:= V_COUNT+1;

ELSE

V_EMAIL_ADDRESS:= V_EMAIL_ADDRESS||' / '||I.EMAIL_ADDRESS;
-- V_PHONE_NUMBER:=V_PHONE_NUMBER||' / '||I.PHONE_NUMBER;

END IF;

END LOOP;

FOR J IN C2
LOOP

IF V_COUNT = 1 THEN

--V_EMAIL_ADDRESS:= I.EMAIL_ADDRESS;
V_PHONE_NUMBER:=J.PHONE_NUMBER;

V_COUNT:= V_COUNT+1;

ELSE

--V_EMAIL_ADDRESS:= V_EMAIL_ADDRESS||' / '||I.EMAIL_ADDRESS;
V_PHONE_NUMBER:=V_PHONE_NUMBER||' / '||J.PHONE_NUMBER;

END IF;

END LOOP;
IF P_NO_ID =1 THEN
RETURN V_EMAIL_ADDRESS;

ELSIF P_NO_ID =2 THEN
RETURN V_PHONE_NUMBER;

ELSE RETURN NULL;
END IF;

EXCEPTION WHEN OTHERS THEN RETURN NULL;
END;
/

Israr Oracle

Monday, October 8, 2018

Customer Data with Bank Report

No comments:

Post a Comment