Hi Team,
I am facing some strange issue. We have created some custom projection in IFS Developer Studio with below query, this query is working and returning set of data in Oracle SQL Developer as well as in IFS Developer studio execte command window, where as same is not returning any data through postman call, it always returns empty object array.
To narrow down the problem, we created one more projection in IFS 10 version with same query, it is working fine and able to see the result in Postman.
Any help is much helpful to resolve the issue.
SELECT
DISTINCT NVL(B.CUSTOMER_ID,A.IDENTITY) AS Account_Code,
B.CUSTOMER_ID,
A.IDENTITY,
IIN.Customer_ID,
NVL(B.NAME,(SELECT NAME FROM CUSTOMER_INFO WHERE CUSTOMER_ID = A.IDENTITY)) AS Account_Name,
'TEST' AS Account_Code_Type,
'1' AS Company_ID,
'1' AS Master_Company,
0 AS Is_Service_Only,
NVL(B.CUSTOMER_ID,A.IDENTITY) AS Intrefcode,
0 AS Is_Private_Label,
B.ADDRESS_ID AS Address_Id,
B.ADDRESS_TYPE_CODE AS Address_Type_Code,
B.ADDRESS1 AS Address1,
B.ADDRESS2 AS Address2,
B.ZIP_CODE AS Zip_Code,
B.CITY AS City,
'' AS Title,
A.NAME AS Name,
A.PHONE AS Phone,
A.EMAIL AS Email,
A.MOBILE AS Mobile,
A.FAX AS Fax, 1 AS Is_Active,
SYSDATE AS To_Date,
--IINCFV.CF$_DISTCAT AS DISTCATE,
A.BARCODECONTACT AS Barcode_Contact
FROM ((SELECT CIT.CUSTOMER_ID, CIT.NAME , CIAT.ADDRESS_ID, CIAT.ADDRESS1, CIAT.ADDRESS2, CIAT.ZIP_CODE, CIAT.CITY, CIATT.ADDRESS_TYPE_CODE FROM CUSTOMER_INFO CIT LEFT OUTER JOIN CUSTOMER_INFO_ADDRESS CIAT ON CIT.CUSTOMER_ID = CIAT.CUSTOMER_ID LEFT OUTER JOIN CUSTOMER_INFO_ADDRESS_TYPE CIATT ON CIAT.CUSTOMER_ID = CIATT.CUSTOMER_ID AND CIAT.ADDRESS_ID = CIATT.ADDRESS_ID AND CIATT.DEF_ADDRESS = 'TRUE') B
FULL OUTER JOIN (SELECT IDENTITY, ADDRESS_ID, NAME, MOBILE, PHONE, EMAIL, FAX, BARCODECONTACT FROM (SELECT CM.IDENTITY, CM.METHOD_ID, CM.NAME, CM.VALUE, CM.ADDRESS_ID, CMC.VALUE AS BARCODECONTACT FROM COMM_METHOD CM LEFT OUTER JOIN COMM_METHOD CMC ON CMC.OBJKEY = CM.OBJKEY WHERE CM.PARTY_TYPE = 'Customer') SOURCE PIVOT (MAX(VALUE) FOR method_id IN ('Mobile' AS MOBILE, 'Phone' AS PHONE, 'E-Mail' AS EMAIL, 'Fax' AS FAX)) PVT )A
ON A.IDENTITY = B.CUSTOMER_ID
AND A.ADDRESS_ID = B.ADDRESS_ID
INNER JOIN IDENTITY_INVOICE_INFO IIN
ON NVL(LTRIM(RTRIM(B.CUSTOMER_ID)),LTRIM(RTRIM(A.IDENTITY))) = LTRIM(RTRIM(IIN.Customer_ID))
INNER JOIN INVOICE_CUSTOMER_TYPE_GROUP ICTG
ON ICTG.Group_ID = IIN.Group_ID AND ICTG.COMPANY = IIN.Company
INNER JOIN CUST_HIERARCHY_STRUCT CHS ON CHS.CUSTOMER_NO = NVL(B.CUSTOMER_ID, A.IDENTITY)
LEFT OUTER JOIN IDENTITY_INVOICE_INFO IINCFV
ON NVL(B.CUSTOMER_ID,A.IDENTITY) = IINCFV.IDENTITY
)
WHERE B.CUSTOMER_ID = 'FINCUST01';
