[Home] [Help]
5341: ,ERROR_MSG
5342: ,MSG_TOKENS)
5343: VALUES (G_WORKER_REQ_ID
5344: ,HEADER_INTERFACE_ROWID
5345: ,'OKS_SALES_CREDITS_INTERFACE'
5346: ,SALES_CREDIT_INTERFACE_ID
5347: ,'OKS_IMP_SC_INVALID_SALEPERS'
5348: ,NULL)
5349: WHEN (SALESGROUP_V = 'N') THEN
5355: ,ERROR_MSG
5356: ,MSG_TOKENS)
5357: VALUES (G_WORKER_REQ_ID
5358: ,HEADER_INTERFACE_ROWID
5359: ,'OKS_SALES_CREDITS_INTERFACE'
5360: ,SALES_CREDIT_INTERFACE_ID
5361: ,'OKS_IMP_SC_INVALID_SALEGRP'
5362: ,NULL)
5363: WHEN (SALES_CREDIT_TYPE_ID IS NULL) THEN
5369: ,ERROR_MSG
5370: ,MSG_TOKENS)
5371: VALUES (G_WORKER_REQ_ID
5372: ,HEADER_INTERFACE_ROWID
5373: ,'OKS_SALES_CREDITS_INTERFACE'
5374: ,SALES_CREDIT_INTERFACE_ID
5375: ,'OKS_IMP_SC_INVALID_SCT'
5376: ,NULL)
5377: WHEN (QUOTA_FLAG = 'Y' AND PERCENT_SUM <> 100) THEN
5383: ,ERROR_MSG
5384: ,MSG_TOKENS)
5385: VALUES (G_WORKER_REQ_ID
5386: ,HEADER_INTERFACE_ROWID
5387: ,'OKS_SALES_CREDITS_INTERFACE'
5388: ,SALES_CREDIT_INTERFACE_ID
5389: ,'OKS_IMP_SC_INVALID_PERSUM'
5390: ,NULL)
5391: /*Changes made for bug10295589*/
5398: ,ERROR_MSG
5399: ,MSG_TOKENS)
5400: VALUES (G_WORKER_REQ_ID
5401: ,HEADER_INTERFACE_ROWID
5402: ,'OKS_SALES_CREDITS_INTERFACE'
5403: ,SALES_CREDIT_INTERFACE_ID
5404: ,'OKS_IMP_SC_INVALID_PER'
5405: ,NULL)
5406: /*bug 10295589 added new when condition to handle non-quota sales credit percentage
5415: ,ERROR_MSG
5416: ,MSG_TOKENS)
5417: VALUES (G_WORKER_REQ_ID
5418: ,HEADER_INTERFACE_ROWID
5419: ,'OKS_SALES_CREDITS_INTERFACE'
5420: ,SALES_CREDIT_INTERFACE_ID
5421: ,'OKS_IMP_SC_INVALID_NQ_PER'
5422: ,NULL)
5423: WHEN (UNIQ = 'N') THEN
5429: ,ERROR_MSG
5430: ,MSG_TOKENS)
5431: VALUES (G_WORKER_REQ_ID
5432: ,HEADER_INTERFACE_ROWID
5433: ,'OKS_SALES_CREDITS_INTERFACE'
5434: ,SALES_CREDIT_INTERFACE_ID
5435: ,'OKS_IMP_SC_SP_SCT_NUNIQ'
5436: ,NULL)
5437: SELECT OSC.SALES_CREDIT_INTERFACE_ID SALES_CREDIT_INTERFACE_ID
5457: ,(CASE WHEN COUNT(DISTINCT SCT.ROWID) OVER (PARTITION BY OSC.ROWID) = 1
5458: THEN MAX(SCT.QUOTA_FLAG) OVER (PARTITION BY OSC.ROWID) ELSE NULL END) QUOTA_FLAG
5459: , SUM(OSC.PERCENT) OVER (PARTITION BY OSC.HEADER_INTERFACE_ID, SCT.QUOTA_FLAG) PERCENT_SUM
5460: , OSC.PERCENT PERCENT
5461: ,(CASE WHEN EXISTS (SELECT 'X' FROM OKS_SALES_CREDITS_INTERFACE
5462: WHERE SALES_CREDIT_INTERFACE_ID <> OSC.SALES_CREDIT_INTERFACE_ID
5463: AND HEADER_INTERFACE_ID = OSC.HEADER_INTERFACE_ID
5464: AND (SALESPERSON_ID = OSC.SALESPERSON_ID OR SALESPERSON_NAME = OSC.SALESPERSON_NAME)
5465: AND SALES_CREDIT_TYPE = OSC.SALES_CREDIT_TYPE)
5463: AND HEADER_INTERFACE_ID = OSC.HEADER_INTERFACE_ID
5464: AND (SALESPERSON_ID = OSC.SALESPERSON_ID OR SALESPERSON_NAME = OSC.SALESPERSON_NAME)
5465: AND SALES_CREDIT_TYPE = OSC.SALES_CREDIT_TYPE)
5466: THEN 'N' ELSE 'Y' END) UNIQ
5467: FROM OKS_SALES_CREDITS_INTERFACE OSC
5468: ,OKS_INT_HEADER_STG_TEMP HDR
5469: ,OE_SALES_CREDIT_TYPES SCT
5470: ,(SELECT distinct(OSCI.SALES_CREDIT_INTERFACE_ID) -- to avoid duplicate joins with tables when some names match with multiple records
5471: ,(CASE WHEN COUNT(DISTINCT SPI.ROWID) OVER (PARTITION BY OSCI.ROW_ID) = 1
5475: ELSE NULL
5476: END) SALESPERSON_ID
5477: FROM (SELECT SCI.ROWID ROW_ID, SCI.SALES_CREDIT_INTERFACE_ID, SCI.SALESPERSON_NAME, SCI.SALESPERSON_ID,
5478: SCI.SALESGROUP_ID, SCI.SALES_CREDIT_TYPE, SCI.PERCENT, HST.OPERATING_UNIT_ID
5479: FROM OKS_SALES_CREDITS_INTERFACE SCI, OKS_INT_HEADER_STG_TEMP HST
5480: WHERE SCI.HEADER_INTERFACE_ID = HST.HEADER_INTERFACE_ID) OSCI
5481: ,JTF_RS_SALESREPS SPI
5482: ,(SELECT SP.ROWID ROW_ID, SPTL.RESOURCE_NAME, SP.ORG_ID, SP.SALESREP_ID
5483: FROM JTF_RS_RESOURCE_EXTNS_TL SPTL, JTF_RS_SALESREPS SP
5492: AND OSC.SALES_CREDIT_TYPE = SCT.NAME (+);
5493:
5494: IF G_STMT_LOG THEN
5495:
5496: SELECT count(1) INTO l_int_count FROM OKS_SALES_CREDITS_INTERFACE OSCI
5497: WHERE EXISTS (SELECT 'X' FROM OKS_INT_HEADER_STG_TEMP
5498: WHERE HEADER_INTERFACE_ID = OSCI.HEADER_INTERFACE_ID);
5499:
5500: SELECT count(1) INTO l_stg_count FROM OKS_INT_SALES_CREDIT_STG_TEMP;