ASN Appointment

ASN Appointment
CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_APPOINTMENT_V" ("TASK_NUMBER", "SUBJECT",

"TYPE", "TASK_TYPE_ID", "TASK_STATUS", "TASK_STATUS_ID", "SCHEDULED_START_DATE",
"SCHEDULED_END_DATE", "PLANNED_START_DATE", "PLANNED_END_DATE", "ACTUAL_START_DATE",
"ACTUAL_END_DATE", "OWNER_ID", "NAME", "OWNER_TYPE_CODE", "OWNER_TYPE",
"CUSTOMER_ID", "CUSTOMER_NAME", "CUSTOMER_NUMBER", "CREATED_BY", "CREATED_BY_ID",
"MEETING_MODE", "DIAL_IN", "CREATION_DATE", "COLLAB_ID", "USER_ID", "TASK_ID", "COPYRIGHT")
AS
SELECT JTV.TASK_NUMBER,
JTV.TASK_NAME SUBJECT,
JTV.TASK_TYPE TYPE,
JTV.TASK_TYPE_ID,
JTV.TASK_STATUS ,
JTV.TASK_STATUS_ID,
JTV.SCHEDULED_START_DATE,
JTV.SCHEDULED_END_DATE,
JTV.PLANNED_START_DATE,
JTV.PLANNED_END_DATE,
JTV.ACTUAL_START_DATE,
JTV.ACTUAL_END_DATE,
JTV.OWNER_ID,
JTV.OWNER NAME,
JTV.OWNER_TYPE_CODE,
JTV.OWNER_TYPE,
JTV.CUSTOMER_ID,
JTV.CUSTOMER_NAME,
JTV.CUSTOMER_NUMBER,
FU.USER_NAME CREATED_BY,
JTV.CREATED_BY CREATED_BY_ID,
CVCDV.MEETING_MODE,
CVCDV.DIAL_IN,
JTV.CREATION_DATE,
---Primary Keys
CVCDV.COLLAB_ID,
FU.USER_ID,
JTV.TASK_ID
, 'Copyright(c) 2001-'||to_char(SYSDATE,'YYYY')||' '||'EiS Technologies Inc. All rights reserved.'

Copyright
--descr#flexfield#start
--descr#flexfield#end
--gl#accountff#start
--gl#accountff#end
FROM FND_USER FU,
JTF_TASKS_V JTV,
CAC_VIEW_COLLAB_DETAILS_VL CVCDV
WHERE 1 =1
AND JTV.CREATED_BY =FU.USER_ID
AND JTV.TASK_ID =CVCDV.TASK_ID
AND JTV.SOURCE_OBJECT_TYPE_CODE='APPOINTMENT'
;
EIS_ASN_COMM_PLAN_ELEMENTS_V
CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_COMM_PLAN_ELEMENTS_V"

("OPERATING_UNIT", "RESOURCE_NAME", "PAYMENT_BATCH", "PLAN_ELEMENT_NAME",
"COMMISSION_AMOUNT", "INCENTIVE_TYPE", "DIRECT_CREDIT_RECEIVER", "TRX_TYPE_CODE",
"PROCESSED_DATE", "PAYEE_SALESREP_ID", "PAYRUN_ID", "QUOTA_ID", "CP_ORG_ID",
"CPT_PAYMENT_TRANSACTION_ID", "CPT_ORG_ID", "CQ_QUOTA_ID", "CQ_ORG_ID", "SALESREP_ID",
"CS_ORG_ID", "HOU_ORGANIZATION_ID", "COPYRIGHT") AS
select
HOU.name OPERATING_UNIT,
CS.name RESOURCE_NAME,
CP.NAME PAYMENT_BATCH,
CQ.name PLAN_ELEMENT_NAME,
NVL(TO_CHAR(CPT.AMOUNT,'9999999999999.99'),0) COMMISSION_AMOUNT,
CPT.INCENTIVE_TYPE_CODE INCENTIVE_TYPE,
CS.name DIRECT_CREDIT_RECEIVER,
CPT.TRX_TYPE TRX_TYPE_CODE,
CPT.PROCESSED_DATE PROCESSED_DATE,
CPT.PAYEE_SALESREP_ID PAYEE_SALESREP_ID,
CPT.PAYRUN_ID PAYRUN_ID,
CPT.QUOTA_ID,
CP.ORG_ID CP_ORG_ID,
-----Primary Keys
CPT.PAYMENT_TRANSACTION_ID CPT_PAYMENT_TRANSACTION_ID,
CPT.ORG_ID CPT_ORG_ID,
CQ.QUOTA_ID CQ_QUOTA_ID,
CQ.ORG_ID CQ_ORG_ID,
CS.SALESREP_ID,
CS.ORG_ID CS_ORG_ID,
HOU.ORGANIZATION_ID HOU_ORGANIZATION_ID

Copyright
--gl#accountff#end
from CN_PAYMENT_TRANSACTIONS CPT,
CN_QUOTAS CQ,
CN_SALESREPS CS,
CN_PAYRUNS CP,
HR_OPERATING_UNITS HOU
where CPT.QUOTA_ID = CQ.QUOTA_ID(+)
and CPT.CREDITED_SALESREP_ID = CS.SALESREP_ID
and CPT.PAYRUN_ID = CP.PAYRUN_ID
and HOU.ORGANIZATION_ID = CS.ORG_ID (+)

CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_COMM_STMT_BAL_SUMM_V"
("OPERATING_UNIT", "RESOURCE_NAME", "PAYMENT_BATCH", "BEGINING_BALANCE", "EARNINGS",
"RECOVERABLE_AMT", "NONRECOVERABLE", "PAYMENT_DUE", "END_BALANCE", "SALESREP_ID",
"PAYRUN_ID", "CS_SALESREP_ID", "CS_ORG_ID", "CP_NAME", "CP_ORG_ID",
"HOU_ORGANIZATION_ID", "COPYRIGHT") AS
select V.OPERATING_UNIT OPERATING_UNIT,
V.SALES_PERSON resource_name,
V.PAYMENT_BATCH PAYMENT_BATCH,
V.BEGIN_BALANCE BEGINING_BALANCE,
V.EARNINGS EARNINGS,
V.REC_DRAW_PD recoverable_amt ,
V.NREC_DRAW_PD nonrecoverable ,
V.BEGIN_BALANCE +V.EARNINGS+V.REC_DRAW_PD+V.NREC_DRAW_PD PAYMENT_DUE,
-V.REC_DRAW_PD END_BALANCE,
V.SALESREP_ID,
V.PAYRUN_ID,
-----Primary Keys
V.CS_SALESREP_ID CS_SALESREP_ID,
V.CS_ORG_ID CS_ORG_ID,
v.CP_NAME CP_NAME,
v.CP_ORG_ID CP_ORG_ID,
v.HOU_ORGANIZATION_ID HOU_ORGANIZATION_ID

Copyright
--gl#accountff#end
FROM
(SELECT WK.HELD_AMOUNT,
(SELECT NVL(SUM(AMOUNT),0)
FROM CN_PAYMENT_TRANSACTIONS
WHERE CREDITED_SALESREP_ID=WK.SALESREP_ID
AND PAYRUN_ID =WK.PAYRUN_ID
AND INCENTIVE_TYPE_CODE IN( 'BONUS','COMMISSION')
AND PAY_PERIOD_ID <
(SELECT PAY_PERIOD_ID FROM CN_PAYRUNS WHERE PAYRUN_ID=WK.PAYRUN_ID
)+
AND INCENTIVE_TYPE_CODE = 'PMTPLN_REC'
) BEGIN_BALANCE,
AND PAY_PERIOD_ID =
(SELECT PAY_PERIOD_ID FROM CN_PAYRUNS WHERE PAYRUN_ID=WK.PAYRUN_ID
)EARNINGS,
WK.PMT_AMOUNT_ADJ_REC +
(SELECT NVL(SUM(PAYMENT_AMOUNT),0)
AND INCENTIVE_TYPE_CODE = 'MANUAL_PAY_ADJ'
AND RECOVERABLE_FLAG ='Y'
) +
(SELECT ( NVL(SUM(PAYMENT_AMOUNT),0) - NVL(SUM(AMOUNT),0))
) REC_DRAW_PD,
WK.PMT_AMOUNT_ADJ_NREC +
(SELECT NVL(SUM(PAYMENT_AMOUNT),0)
AND INCENTIVE_TYPE_CODE = 'MANUAL_PAY_ADJ'
AND RECOVERABLE_FLAG ='N'
) +
(SELECT NVL(-1 *SUM(PAYMENT_AMOUNT),0)
AND INCENTIVE_TYPE_CODE = 'PMTPLN_REC'
AND WAIVE_FLAG ='Y'
) NREC_DRAW_PD,
WK.PAYRUN_ID PAYRUN_ID ,
WK.SALESREP_ID SALESREP_ID,
CS.name SALES_PERSON,
CP.name PAYMENT_BATCH,
HOU.NAME OPERATING_UNIT,
CS.SALESREP_ID CS_SALESREP_ID,
CS.ORG_ID CS_ORG_ID,
CP.name CP_NAME,
CP.ORG_ID CP_ORG_ID,
hou.ORGANIZATION_ID hou_ORGANIZATION_ID
FROM CN_PAYMENT_WORKSHEETS WK,
CN_SALESREPS CS,
CN_PAYRUNS CP,
WHERE WK.SALESREP_ID=CS.SALESREP_ID
and WK.PAYRUN_ID =CP.PAYRUN_ID
and CS.ORG_ID=HOU.ORGANIZATION_ID
--AND WK.SALESREP_ID = 100000004
-- AND WK.PAYRUN_ID =2654
and WK.QUOTA_ID(+) is null
)V
;
CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_DAYS_SOVS_V" ("OPPORTUNITY_NAME",
"OPPORTUNITY_NUMBER", "CREATION_DATE", "CREATED_BY_ID", "CREATED_BY", "CUSTOMER",
"PARTY_TYPE", "CUSTOMER_ID", "PARTY_NUMBER", "STATUS", "SALES_CHANNEL",
"SOURCE_PROMOTION_ID", "CYCLE", "CYCLE_DESCRIPTION", "TOTAL_AMOUNT", "CURRENCY_CODE",
"DECISION_TIMEFRAME_CODE", "PRODUCT_CATEGORY_ID", "PRODUCT_CAT_SET_ID",
"PRODUCT_CATEGORY", "PRODUCT", "PRODUCT_UOM", "QUANTITY", "PRODUCT_AMOUNT",
"EMPLOYEES_TOTAL", "EQUAL_TO_0", "BETWEEN_1_9", "BETWEEN_10_29", "BETWEEN_30_59",
"BETWEEN_60_89", "BETWEEN_90_179", "BETWEEN_180_365", "GREATER_THAN_365", "LEAD_ID",
"FU_USER_ID", "PARTY_ID", "SALES_CHANNEL_CODE", "SALES_STAGE_ID", "LEAD_LINE_ID",
"CATEGORY_ID", "INVENTORY_ITEM_ID", "ORGANIZATION_ID", "UOM_CODE", "ASSAT_LANGUAGE",
"MUOMT_LANGUAGE", "COPYRIGHT") AS
SELECT AL.DESCRIPTION OPPORTUNITY_NAME,
AL.LEAD_NUMBER OPPORTUNITY_NUMBER,
AL.CREATION_DATE,
AL.CREATED_BY CREATED_BY_ID,
HP.PARTY_NAME CUSTOMER,
HP.PARTY_TYPE,
AL.CUSTOMER_ID,
HP.PARTY_NUMBER,
AL.STATUS STATUS,
ASO_CHANNEL.SALES_CHANNEL SALES_CHANNEL,
AL.SOURCE_PROMOTION_ID ,
ASSAT.NAME CYCLE,
ASSAT.DESCRIPTION CYCLE_DESCRIPTION,
AL.TOTAL_AMOUNT,
AL.CURRENCY_CODE,
AL.DECISION_TIMEFRAME_CODE,
ALLA.PRODUCT_CATEGORY_ID,
ALLA.PRODUCT_CAT_SET_ID,
MC.DESCRIPTION PRODUCT_CATEGORY,
NVL(MSIK.DESCRIPTION,MC.DESCRIPTION) PRODUCT,
MUOMT.UNIT_OF_MEASURE PRODUCT_UOM,
ALLA.QUANTITY,
ALLA.TOTAL_AMOUNT PRODUCT_AMOUNT,
HP.EMPLOYEES_TOTAL,
CASE
WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) =0
THEN AL.TOTAL_AMOUNT
END EQUAL_TO_0,
CASE
WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) BETWEEN 1 AND 9
END BETWEEN_1_9,
CASE
END BETWEEN_10_29,
CASE
END BETWEEN_30_59,
CASE
END BETWEEN_60_89,
CASE
END BETWEEN_90_179,
CASE
END BETWEEN_180_365,
CASE
WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) >365
END GREATER_THAN_365,
-----PRIMARY KEYS
AL.LEAD_ID LEAD_ID,
FU.USER_ID FU_USER_ID,
HP.PARTY_ID,
ASO_CHANNEL.SALES_CHANNEL_CODE,
ASSAT.SALES_STAGE_ID,
ALLA.LEAD_LINE_ID ,
MC.CATEGORY_ID,
MSIK.INVENTORY_ITEM_ID,
MSIK.ORGANIZATION_ID,
MUOMT.UOM_CODE,
ASSAT.LANGUAGE ASSAT_LANGUAGE,
MUOMT.LANGUAGE MUOMT_LANGUAGE

Copyright
--DESCR#FLEXFIELD#START
--DESCR#FLEXFIELD#END
--GL#ACCOUNTFF#START
--GL#ACCOUNTFF#END
FROM AS_LEADS AL,
FND_USER FU,
HZ_PARTIES HP,
ASO_I_SALES_CHANNELS_V ASO_CHANNEL,
AS_SALES_STAGES_ALL_TL ASSAT,
AS_LEAD_LINES_ALL ALLA,
MTL_CATEGORIES MC,
MTL_SYSTEM_ITEMS_KFV MSIK,
MTL_UNITS_OF_MEASURE_TL MUOMT
WHERE AL.CREATED_BY =FU.USER_ID
AND AL.CUSTOMER_ID =HP.PARTY_ID
AND AL.CHANNEL_CODE = ASO_CHANNEL.SALES_CHANNEL_CODE(+)
AND AL.SALES_STAGE_ID =ASSAT.SALES_STAGE_ID(+)
AND ASSAT.LANGUAGE(+) = USERENV('LANG')
AND AL.LEAD_ID =ALLA.LEAD_ID(+)
AND ALLA.PRODUCT_CATEGORY_ID=MC.CATEGORY_ID(+)
AND ALLA.INVENTORY_ITEM_ID =MSIK.INVENTORY_ITEM_ID(+)
AND ALLA.ORGANIZATION_ID =MSIK.ORGANIZATION_ID(+)
AND ALLA.UOM_CODE =MUOMT.UOM_CODE(+)
AND MUOMT.LANGUAGE(+)=USERENV('LANG')
--AND AL.LEAD_ID NOT IN (SELECT OPPORTUNITY_ID FROM AS_SALES_LEAD_OPPORTUNITY)
AND AL.STATUS = 'WON'
AND AL.DESCRIPTION IS NOT NULL
ORDER BY 3
;
CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_DAYS_WIN_V" ("OPPORTUNITY_NAME",
"OPPORTUNITY_NUMBER", "CREATION_DATE", "CREATED_BY_ID", "CREATED_BY", "CUSTOMER",
"PARTY_TYPE", "CUSTOMER_ID", "PARTY_NUMBER", "STATUS", "SALES_CHANNEL",
"SOURCE_PROMOTION_ID", "CYCLE", "CYCLE_DESCRIPTION", "TOTAL_AMOUNT", "CURRENCY_CODE",
"DECISION_TIMEFRAME_CODE", "PRODUCT_CATEGORY_ID", "PRODUCT_CAT_SET_ID",
"PRODUCT_CATEGORY", "PRODUCT", "PRODUCT_UOM", "QUANTITY", "PRODUCT_AMOUNT",
"EMPLOYEES_TOTAL", "EQUAL_TO_0", "BETWEEN_1_9", "BETWEEN_10_29", "BETWEEN_30_59",
"BETWEEN_60_89", "BETWEEN_90_179", "BETWEEN_180_365", "GREATER_THAN_365", "LEAD_ID",
"FU_USER_ID", "PARTY_ID", "SALES_CHANNEL_CODE", "SALES_STAGE_ID", "LEAD_LINE_ID",
"CATEGORY_ID", "INVENTORY_ITEM_ID", "ORGANIZATION_ID", "UOM_CODE", "LANGUAGE",
AL.CREATION_DATE,
HP.PARTY_TYPE,
AL.CUSTOMER_ID,
HP.PARTY_NUMBER,
AL.STATUS STATUS,
ASSAT.NAME CYCLE,
AL.TOTAL_AMOUNT,
AL.CURRENCY_CODE,
ALLA.QUANTITY,
HP.EMPLOYEES_TOTAL,
CASE
WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) =0
THEN 'WON'
END EQUAL_TO_0,
CASE
THEN 'WON'
END BETWEEN_1_9,
CASE
THEN 'WON'
END BETWEEN_10_29,
CASE
THEN 'WON'
END BETWEEN_30_59,
CASE
THEN 'WON'
END BETWEEN_60_89,
CASE
THEN 'WON'
END BETWEEN_90_179,
CASE
THEN 'WON'
END BETWEEN_180_365,
CASE
WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) >365
THEN 'WON'
END GREATER_THAN_365,
-----PRIMARY KEYS
AL.LEAD_ID LEAD_ID,
HP.PARTY_ID,
ALLA.LEAD_LINE_ID ,
MC.CATEGORY_ID,
MUOMT.UOM_CODE,
ASSAT.LANGUAGE,

Copyright
--GL#ACCOUNTFF#END
FROM AS_LEADS AL,
FND_USER FU,
HZ_PARTIES HP,
MTL_CATEGORIES MC,
AND MUOMT.LANGUAGE(+) = USERENV('LANG')
--AND AL.LEAD_ID NOT IN (SELECT OPPORTUNITY_ID FROM AS_SALES_LEAD_OPPORTUNITY)
AND AL.STATUS = 'WON'
ORDER BY 3
;
CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_FORECAST_DETAILS_V" ("CATEGORY", "PERIOD",
"PRODUCT_CATEGORY_ID", "FORECAST_CATEGORY_ID", "BEST_FORECAST_AMOUNT",
"FORECAST_AMOUNT", "WORST_FORECAST_AMOUNT", "FORECAST_ID", "CURRENCY_CODE",
"STATUS_CODE", "FORECAST_WORKSHEET_ID", "OPP_WORST_FORECAST_AMOUNT",
"OPP_FORECAST_AMOUNT", "OPP_BEST_FORECAST_AMOUNT", "CREATION_DATE", "CREATED_BY_ID",
"CREATED_BY", "PROD_WORKSHEET_LINE_ID", "FU_USER_ID", "COPYRIGHT") AS
SELECT AFC.FORECAST_CATEGORY_NAME CATEGORY,
APWL.PERIOD_NAME PERIOD,
APWL.PRODUCT_CATEGORY_ID,
apwl.forecast_category_id,
APWL.BEST_FORECAST_AMOUNT,
APWL.FORECAST_AMOUNT,
APWL.WORST_FORECAST_AMOUNT,
APWL.FORECAST_ID,
APWL.CURRENCY_CODE,
APWL.STATUS_CODE,
APWL.FORECAST_WORKSHEET_ID,
APWL.OPP_WORST_FORECAST_AMOUNT,
APWL.OPP_FORECAST_AMOUNT,
APWL.OPP_BEST_FORECAST_AMOUNT,
APWL.CREATION_DATE,
APWL.CREATED_BY CREATED_BY_ID ,
-----Primary Keys
APWL.PROD_WORKSHEET_LINE_ID,
FU.USER_ID FU_USER_ID

Copyright
--gl#accountff#end
FROM AS_PROD_WORKSHEET_LINES APWL,
AS_FORECAST_CATEGORIES_VL AFC,
fnd_user fu
WHERE APWL.FORECAST_CATEGORY_ID=AFC.FORECAST_CATEGORY_ID
AND APWL.created_by =fu.user_id
;
CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_FORECAST_HISTORY_V" ("SUBMITTED_DATE",
"FORECAST_ID", "CATEGORY", "PERIOD_NAME", "CURRENCY_CODE", "STATUS_CODE", "SAVE_DATE",
"BEST_FORECAST_AMOUNT", "FORECAST_AMOUNT", "WORST_FORECAST_AMOUNT",
"FORECAST_CATEGORY_ID", "CREATED_BY_ID", "CREATED_BY", "FORECAST_WORKSHEET_ID",
"FU_USER_ID", "COPYRIGHT") AS
SELECT APW.CREATION_DATE SUBMITTED_DATE,
APW.FORECAST_ID,
APW.WORKSHEET_TYPE CATEGORY,
APW.PERIOD_NAME,
APW.CURRENCY_CODE,
APW.STATUS_CODE,
APW.SAVE_DATE,
APW.BEST_FORECAST_AMOUNT,
APW.FORECAST_AMOUNT,
APW.WORST_FORECAST_AMOUNT,
APW.FORECAST_CATEGORY_ID,
APW.CREATED_BY CREATED_BY_ID,
-----Primary Keys
APW.FORECAST_WORKSHEET_ID,

Copyright
--gl#accountff#end
FROM AS_FORECAST_WORKSHEETS APW,
FND_USER FU
WHERE APW.CREATED_BY=FU.USER_ID
CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_LEAD_OPPORTUNITIES_V"

("OPPORTUNITY_NUMBER", "OPPORTUNITY", "OPP_CREATION_DATE", "OPERATING_UNIT",
"OPP_CREATED_BY", "OPP_CREATED_BY_ID", "OPP_CUSTOMER", "CLOSE_DATE", "OPP_STATUS",
"OPP_STAGE", "OPP_SALES_CHANNEL", "OPP_AMOUNT", "OPP_CURRENCY_CODE",
"WIN_PROBABILITY", "OPP_CLOSE_REASON", "SALES_METHODOLOGY",
"SALES_METHODOLOGY_DESCRIPTION", "ADDRESS1", "ADDRESS2", "ADDRESS3", "ADDRESS4", "CITY",
"POSTAL_CODE", "STATE", "COUNTRY", "LEAD_NAME", "LEAD_NUMBER", "CREATION_DATE",
"CREATED_BY_ID", "CREATED_BY", "CUSTOMER", "PARTY_TYPE", "CUSTOMER_ID", "PARTY_NUMBER",
"ASSIGN_TO_PERSON_ID", "ASSIGN_TO_SALESFORCE_ID", "ASSIGNED_NAME", "PRIMARY_CONTACT",
"PHONE_NUM", "LEAD_STATUS", "SALES_CHANNEL", "SOURCE_PROMOTION_ID", "RANK",
"BUDGET_AMOUNT", "BUDGET_STATUS_CODE", "BUDGET_STATUS", "CURRENCY_CODE",
"DECISION_TIMEFRAME_CODE", "PARTY_SITE_ID", "OPPORTUNITY_ID", "LOCATION_ID",
"SALES_METHODOLOGY_ID", "ORGANIZATION_ID", "LEAD_ID", "USER_ID", "PARTY_ID",
"SALES_STAGE_ID", "ORG_ID", "LEAD_OPPORTUNITY_ID", "COPYRIGHT") AS
SELECT AL.LEAD_NUMBER OPPORTUNITY_NUMBER,
AL.DESCRIPTION OPPORTUNITY,
AL.CREATION_DATE OPP_CREATION_DATE,
OOD.ORGANIZATION_NAME OPERATING_UNIT,
FU.USER_NAME OPP_CREATED_BY,
FU.CREATED_BY opp_CREATED_BY_ID,
HP.PARTY_NAME OPP_CUSTOMER,
AL.DECISION_DATE CLOSE_DATE,
AL.STATUS OPP_STATUS,
ASSV.NAME OPP_STAGE,
AL.CHANNEL_CODE OPP_SALES_CHANNEL,
AL.TOTAL_AMOUNT OPP_AMOUNT,
AL.CURRENCY_CODE OPP_CURRENCY_CODE,
AL.WIN_PROBABILITY,
al.close_reason opp_close_reason,
asml.name SALES_METHODOLOGY,
asml.description SALES_METHODOLOGY_description,
HL.ADDRESS1 ,
HL.ADDRESS2,
HL.ADDRESS3,
HL.ADDRESS4,
HL.CITY,
HL.POSTAL_CODE,
HL.STATE,
HL.COUNTRY,
-- ARC_SOURCE_CODE_FOR ,
/* DECODE (amsc.arc_source_code_for , 'CAMP',
(SELECT campaign_name
FROM AMS_CAMPAIGNS_ALL_TL
WHERE campaign_id = amsc.source_code_for_id
), 'EVEH',
(SELECT event_header_name
FROM AMS_EVENT_HEADERS_ALL_TL
WHERE event_header_id = amsc.source_code_for_id
), 'EONE',
(SELECT event_offer_name
FROM AMS_EVENT_OFFERS_ALL_TL
WHERE event_offer_id = amsc.source_code_for_id
), 'EVEO',
), 'CSCH',
(SELECT schedule_name
FROM ams_campaign_schedules_tl
WHERE schedule_id = amsc.source_code_for_id
), 'OFFR',
(SELECT description
FROM qp_list_headers_tl
WHERE LIST_HEADER_ID = AMSC.SOURCE_CODE_FOR_ID
), NULL ) SOURCE, */
ASLV.DESCRIPTION LEAD_NAME,
ASLV.LEAD_NUMBER LEAD_NUMBER,
ASLV.CREATION_DATE,
ASLV.CREATED_BY CREATED_BY_ID,
ASLV.CUSTOMER_NAME CUSTOMER,
ASLV.PARTY_TYPE,
ASLV.CUSTOMER_ID,
ASLV.PARTY_NUMBER,
ASLV.ASSIGN_TO_PERSON_ID,
ASLV.ASSIGN_TO_SALESFORCE_ID,
ASLV.ASSIGNED_NAME,
ASLV.PRIMARY_CONTACT_NAME PRIMARY_CONTACT,
ASLV.PHONE_NUM,
ASLV.LEAD_STATUS,
ASLV.SALES_CHANNEL,
ASLV.SOURCE_PROMOTION_ID ,
ASLV.RANK,
ASLV.BUDGET_AMOUNT,
ASLV.BUDGET_STATUS_CODE,
ASLV.BUDGET_STATUS,
ASLV.CURRENCY_CODE,
ASLV.DECISION_TIMEFRAME_CODE,
--Primary Keys
HPS.PARTY_SITE_ID,
AL.LEAD_ID OPPORTUNITY_ID,
HL.LOCATION_ID,
asml.SALES_METHODOLOGY_ID,
OOD.ORGANIZATION_ID,
ASLV.SALES_LEAD_ID LEAD_ID,
FU.USER_ID,
HP.PARTY_ID,
ASSV.SALES_STAGE_ID,
ASSV.ORG_ID,
-- AMSC.SOURCE_CODE_ID,
ASLO.LEAD_OPPORTUNITY_ID

Copyright
--gl#accountff#end
FROM AS_LEADS AL,
ORG_ORGANIZATION_DEFINITIONS OOD,
FND_USER FU,
HZ_PARTIES HP,
AS_SALES_STAGES_VL ASSV,
HZ_PARTY_SITES HPS,
HZ_LOCATIONS HL,
AS_SALES_METHODOLOGY_VL ASML,
-- AMS_SOURCE_CODES AMSC,
AS_SALES_LEAD_OPPORTUNITY ASLO,
AS_SALES_LEADS_V ASLV
WHERE 1 =1
AND AL.ORG_ID =OOD.ORGANIZATION_ID
AND AL.CREATED_BY =FU.USER_ID
AND ASSV.SALES_STAGE_ID =AL.SALES_STAGE_ID
AND AL.ORG_ID =ASSV.ORG_ID
AND HPS.LOCATION_ID =HL.LOCATION_ID
AND HPS.PARTY_SITE_ID =AL.ADDRESS_ID
AND AL.SALES_METHODOLOGY_ID=ASML.SALES_METHODOLOGY_ID
-- AND AMSC.SOURCE_CODE_ID =AL.SOURCE_PROMOTION_ID
AND AL.LEAD_ID =ASLO.OPPORTUNITY_ID
AND ASLV.sales_lead_id =aslo.sales_lead_id
;
CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_LEAD_TASKS_V" ("LEAD_NAME",
"LEAD_NUMBER", "CREATION_DATE", "CREATED_BY_ID", "CREATED_BY", "CUSTOMER", "PARTY_TYPE",
"CUSTOMER_ID", "PARTY_NUMBER", "ASSIGN_TO_PERSON_ID", "ASSIGN_TO_SALESFORCE_ID",
"ASSIGNED_NAME", "PRIMARY_CONTACT", "PHONE_NUM", "LEAD_STATUS", "SALES_CHANNEL",
"SOURCE", "SOURCE_PROMOTION_ID", "RANK", "BUDGET_AMOUNT", "BUDGET_STATUS_CODE",
"BUDGET_STATUS", "CURRENCY_CODE", "DECISION_TIMEFRAME_CODE", "TASK_NUMBER", "SUBJECT",
"TYPE", "TASK_TYPE_ID", "TASK_STATUS", "TASK_STATUS_ID", "DUE_DATE", "OWNER_ID", "OWNER",
"OWNER_TYPE_CODE", "OWNER_TYPE", "CUSTOMER_NUMBER", "LEAD_ID", "TASK_ID", "FU_USER_ID",
"COPYRIGHT") AS
SELECT ASLV.DESCRIPTION LEAD_NAME,
ASLV.CREATION_DATE,
ASLV.PARTY_TYPE,
ASLV.CUSTOMER_ID,
ASLV.PARTY_NUMBER,
ASLV.ASSIGNED_NAME,
ASLV.PHONE_NUM,
ASLV.LEAD_STATUS,
ASLV.SALES_CHANNEL,
ASLV.SOURCE_PROMOTION_NAME SOURCE,
ASLV.RANK,
ASLV.BUDGET_AMOUNT,
ASLV.BUDGET_STATUS,
ASLV.CURRENCY_CODE,
JTV.TASK_NUMBER,
JTV.TASK_TYPE TYPE,
JTV.TASK_TYPE_ID,
JTV.TASK_STATUS TASK_STATUS,
JTV.TASK_STATUS_ID,
JTV.SCHEDULED_END_DATE DUE_DATE,
JTV.OWNER_ID,
JTV.OWNER,
JTV.OWNER_TYPE,
-----Primary Keys
JTV.TASK_ID,

Copyright
--gl#accountff#end
FROM AS_SALES_LEADS_V ASLV,
FND_USER FU,
JTF_TASKS_V JTV
WHERE ASLV.CREATED_BY =FU.USER_ID
AND ASLV.SALES_LEAD_ID=JTV.SOURCE_OBJECT_ID
;
CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_LEADS_V" ("LEAD_NAME", "LEAD_NUMBER",

"CREATION_DATE", "CREATED_BY_ID", "CREATED_BY", "CUSTOMER", "PARTY_TYPE", "CUSTOMER_ID",
"PARTY_NUMBER", "ASSIGN_TO_PERSON_ID", "ASSIGN_TO_SALESFORCE_ID", "ASSIGNED_NAME",
"PRIMARY_CONTACT", "PHONE_NUM", "LEAD_STATUS", "SALES_CHANNEL", "SOURCE",
"SOURCE_PROMOTION_ID", "RANK", "BUDGET_AMOUNT", "BUDGET_STATUS_CODE",
"BUDGET_STATUS", "CURRENCY_CODE", "DECISION_TIMEFRAME_CODE", "LEAD_ID", "FU_USER_ID",
"COPYRIGHT") AS
SELECT ASLV.DESCRIPTION LEAD_NAME,
ASLV.CREATION_DATE,
ASLV.PARTY_TYPE,
ASLV.CUSTOMER_ID,
ASLV.PARTY_NUMBER,
ASLV.ASSIGNED_NAME,
ASLV.PHONE_NUM,
ASLV.LEAD_STATUS,
ASLV.SALES_CHANNEL,
ASLV.SOURCE_PROMOTION_NAME SOURCE,
ASLV.RANK,
ASLV.BUDGET_AMOUNT,
ASLV.BUDGET_STATUS,
ASLV.CURRENCY_CODE,
-----Primary Keys

Copyright
--gl#accountff#end
FROM
AS_SALES_LEADS_V ASLV,
FND_USER FU
WHERE ASLV.CREATED_BY=FU.USER_ID
;
CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_OPP_QUT_SALEORDER_V"
("OPPORTUNITY_NUMBER", "OPPORTUNITY_NAME", "QUOTE_NAME", "SALES_ORDER_NUMBER",
"REFERENCE_NUMBER", "ORDERED_DATE", "BOOKED_DATE", "ORDER_STATUS", "ORG_ID",
"OPERATING_UNIT", "QUOTE_SOURCE_CODE", "CUSTOMER", "CONTACT", "CHANNEL_CODE",
"AMOUNT", "EXPIRATION_DATE", "QUOTE_HEADER_ID", "ORDER_ID", "SHIP_TO_LOCATION",
"SHIP_TO_ADDRESS1", "SHIP_TO_ADDRESS2", "SHIP_TO_ADDRESS3", "SHIP_TO_ADDRESS4",
"SHIP_TO_ADDRESS5", "SHIP_TO_CITY", "SHIP_TO_COUNTRY", "SHIP_TO_STATE",
"SHIP_TO_POSTAL_CODE", "BILL_TO_LOCATION", "BILL_TO_ADDRESS1", "BILL_TO_ADDRESS2",
"BILL_TO_ADDRESS3", "BILL_TO_ADDRESS4", "BILL_TO_ADDRESS5", "BILL_TO_COUNTRY",
"BILL_TO_STATE", "BILL_TO_POSTAL_CODE", "SALES_PERSON_NAME", "SHIPPING_METHOD_CODE",
"FOB_POINT_CODE", "FREIGHT_TERMS_CODE", "FREIGHT_CARRIER_CODE", "PACKING_INSTRUCTIONS",
"PURCHASE_ORDER_NUMBER", "PART_NUMBER", "REQUEST_DATE", "UOM", "SHIPPED",
"ORDERED_QUANTITY", "STATUS", "PRICE", "TAX", "LEAD_ID", "QUOTE_NUMBER", "SALES_LEAD_ID",
"RELATED_OBJECT_ID", "QUOTE_VERSION", "LEAD_OPPORTUNITY_ID", "LEAD_CONTACT_ID",
"HEADER_ID", "LINE_ID", "SITE_USE_ID", "HZCS_BILL_TO_SITE_USE_ID", "CUST_ACCT_SITE_ID",
"HCAS_BILL_TO_CUST_ACCT_SITE_ID", "PARTY_SITE_ID", "HZPS_BILL_TO_PARTY_SITE_ID",
"LOCATION_ID", "HZL_BILL_TO_LOCATION_ID", "SALESREP_ID", "REP_ORG_ID", "ORGANIZATION_ID",
"PARTY_ID", "COPYRIGHT") AS
AL.DESCRIPTION OPPORTUNITY_NAME,
AQH.QUOTE_NAME QUOTE_NAME,
OOH.ORDER_NUMBER SALES_ORDER_NUMBER,
OOH.SOURCE_DOCUMENT_ID REFERENCE_NUMBER,
OOH.ORDERED_DATE ORDERED_DATE,
OOH.BOOKED_DATE BOOKED_DATE,
OOH.FLOW_STATUS_CODE ORDER_STATUS,
AQH.ORG_ID ORG_ID,
HOU.NAME OPERATING_UNIT,
AQH.QUOTE_SOURCE_CODE QUOTE_SOURCE_CODE,
HZ.PARTY_NAME CUSTOMER ,
(SELECT NVL(HPP.PERSON_NAME,NULL)
FROM HZ_ORG_CONTACTS_CPUI_V HOC,
HZ_PERSON_PROFILES_CPUI_V HPP
WHERE ASLC.CONTACT_PARTY_ID = HOC.RELATIONSHIP_PARTY_ID

AND HOC.OBJECT_ID = ASLC.CUSTOMER_ID
AND HOC.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HOC.SUBJECT_ID = HPP.PARTY_ID
AND HOC.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
)CONTACT,
AQH.SALES_CHANNEL_CODE CHANNEL_CODE,
AQH.TOTAL_QUOTE_PRICE AMOUNT,
AQH.QUOTE_EXPIRATION_DATE EXPIRATION_DATE,
AQH.QUOTE_HEADER_ID ,
AQH.ORDER_ID,
HZCS_SHIP_TO.LOCATION SHIP_TO_LOCATION,
HZL_SHIP_TO.ADDRESS1 SHIP_TO_ADDRESS1,
DECODE (HZL_SHIP_TO.CITY, NULL, NULL, HZL_SHIP_TO.CITY
|| ', ' )
|| DECODE (HZL_SHIP_TO.STATE, NULL, HZL_SHIP_TO.PROVINCE
|| ', ', HZL_SHIP_TO.STATE
|| ', ' )
|| DECODE (HZL_SHIP_TO.POSTAL_CODE, NULL, NULL, HZL_SHIP_TO.POSTAL_CODE
|| ', ' )
|| DECODE (HZL_SHIP_TO.COUNTRY, NULL, NULL, HZL_SHIP_TO.COUNTRY) SHIP_TO_ADDRESS5,
HZL_SHIP_TO.CITY SHIP_TO_CITY,
HZL_SHIP_TO.COUNTRY SHIP_TO_COUNTRY,
HZL_SHIP_TO.STATE SHIP_TO_STATE,
HZL_SHIP_TO.POSTAL_CODE SHIP_TO_POSTAL_CODE,
HZCS_BILL_TO.LOCATION BILL_TO_LOCATION,
HZL_BILL_TO.ADDRESS1 BILL_TO_ADDRESS1,
DECODE (HZL_BILL_TO.CITY, NULL, NULL, HZL_BILL_TO.CITY
|| ', ' )
|| DECODE (HZL_BILL_TO.STATE, NULL, HZL_BILL_TO.PROVINCE
|| ', ', HZL_BILL_TO.STATE
|| ', ' )
|| DECODE (HZL_BILL_TO.POSTAL_CODE, NULL, NULL, HZL_BILL_TO.POSTAL_CODE
|| ', ' ) BILL_TO_ADDRESS5,
HZL_BILL_TO.COUNTRY BILL_TO_COUNTRY,
HZL_BILL_TO.STATE BILL_TO_STATE,
HZL_BILL_TO.POSTAL_CODE BILL_TO_POSTAL_CODE,
REP.NAME SALES_PERSON_NAME,
OOH.SHIPPING_METHOD_CODE SHIPPING_METHOD_CODE,
OOH.FOB_POINT_CODE FOB_POINT_CODE,
OOH.FREIGHT_TERMS_CODE FREIGHT_TERMS_CODE,
OOH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER_CODE,
OOH.PACKING_INSTRUCTIONS PACKING_INSTRUCTIONS,
OOH.CUST_PO_NUMBER PURCHASE_ORDER_NUMBER,
OOL.ORDERED_ITEM PART_NUMBER,
OOL.REQUEST_DATE REQUEST_DATE,
OOL.ORDER_QUANTITY_UOM UOM,
OOL.SHIPPED_QUANTITY SHIPPED,
OOL.ORDERED_QUANTITY ORDERED_QUANTITY,
OOL.FLOW_STATUS_CODE STATUS,
OOL.UNIT_SELLING_PRICE PRICE,
OOL.TAX_VALUE TAX,
--PRIMARY KEYS
AL.LEAD_ID,
AQH.QUOTE_NUMBER QUOTE_NUMBER,
ASL.SALES_LEAD_ID ,
AQRO.RELATED_OBJECT_ID,
AQH.QUOTE_VERSION,
ASLP.LEAD_OPPORTUNITY_ID,
ASLC.LEAD_CONTACT_ID,
OOH.HEADER_ID,
OOL.LINE_ID,
HZCS_SHIP_TO.SITE_USE_ID,
HZCS_BILL_TO.SITE_USE_ID HZCS_BILL_TO_SITE_USE_ID,
HCAS_SHIP_TO.CUST_ACCT_SITE_ID,
HCAS_BILL_TO.CUST_ACCT_SITE_ID HCAS_BILL_TO_CUST_ACCT_SITE_ID,
HZPS_SHIP_TO.PARTY_SITE_ID,
HZPS_BILL_TO.PARTY_SITE_ID HZPS_BILL_TO_PARTY_SITE_ID,
HZL_SHIP_TO.LOCATION_ID,
HZL_BILL_TO.LOCATION_ID HZL_BILL_TO_LOCATION_ID,
REP.SALESREP_ID,
REP.ORG_ID REP_ORG_ID,
HOU.ORGANIZATION_ID,
HZ.PARTY_ID

Copyright
--gl#accountff#end
FROM AS_LEADS AL,
ASO_QUOTE_RELATED_OBJECTS AQRO,
ASO_QUOTE_HEADERS AQH,
HZ_PARTIES HZ,
AS_SALES_LEADS ASL,
AS_SALES_LEAD_OPPORTUNITY ASLP,
AS_SALES_LEAD_CONTACTS ASLC,
OE_ORDER_HEADERS OOH,
OE_ORDER_LINES OOL,
HZ_CUST_SITE_USES HZCS_SHIP_TO,
HZ_CUST_ACCT_SITES HCAS_SHIP_TO,
HZ_PARTY_SITES HZPS_SHIP_TO,
HZ_LOCATIONS HZL_SHIP_TO,
HZ_CUST_SITE_USES HZCS_BILL_TO,
HZ_CUST_ACCT_SITES HCAS_BILL_TO,
HZ_PARTY_SITES HZPS_BILL_TO,
HZ_LOCATIONS HZL_BILL_TO,
RA_SALESREPS REP,
WHERE AQRO.QUOTE_OBJECT_TYPE_CODE = 'HEADER'
AND AQRO.RELATIONSHIP_TYPE_CODE = 'OPP_QUOTE'
AND AL.LEAD_ID = AQRO.OBJECT_ID
AND AQH.QUOTE_HEADER_ID = AQRO.QUOTE_OBJECT_ID
AND AL.LEAD_ID = ASLP.OPPORTUNITY_ID
AND ASLP.SALES_LEAD_ID = ASL.SALES_LEAD_ID
AND HZ.PARTY_ID = ASL.CUSTOMER_ID
AND ASL.SALES_LEAD_ID = ASLC.SALES_LEAD_ID(+)
AND OOH.HEADER_ID = AQH.ORDER_ID
AND OOH.HEADER_ID = OOL.HEADER_ID
--## Ship To Joins##--
AND OOH.SHIP_TO_ORG_ID = HZCS_SHIP_TO.SITE_USE_ID(+)

AND HZCS_SHIP_TO.CUST_ACCT_SITE_ID = HCAS_SHIP_TO.CUST_ACCT_SITE_ID(+)
AND HCAS_SHIP_TO.PARTY_SITE_ID = HZPS_SHIP_TO.PARTY_SITE_ID(+)
AND HZL_SHIP_TO.LOCATION_ID(+) = HZPS_SHIP_TO.LOCATION_ID
--## Bill To Joins ##--
AND OOH.INVOICE_TO_ORG_ID = HZCS_BILL_TO.SITE_USE_ID(+)
AND HZCS_BILL_TO.CUST_ACCT_SITE_ID = HCAS_BILL_TO.CUST_ACCT_SITE_ID(+)
AND HCAS_BILL_TO.PARTY_SITE_ID = HZPS_BILL_TO.PARTY_SITE_ID(+)
AND HZL_BILL_TO.LOCATION_ID(+) = HZPS_BILL_TO.LOCATION_ID
AND OOH.SALESREP_ID = REP.SALESREP_ID(+)
AND OOH.ORG_ID = HOU.ORGANIZATION_ID
;
CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_OPPITEM_CHANNEL_SELL_V"
("OPPERTUNITY_NUMBER", "OPPERTUNITY_NAME", "CUSTOMER", "ADDRESS", "CLOSED_DATE",
"METHODLOGY", "SATGE", "STAUS", "CLOSE_REASON", "WIN_PROBABILITY", "SALES_CHANNEL",
"AMOUNT", "CURRENCY", "OPERATING_UNIT", "PRODUCT", "ITEM_NAME", "QUANTITY", "UOM",
"TOTAL_AMOUNT", "FORECAST_DATE", "WORST", "FORECAST", "BEST", "REVENUE_AMOUNT",
"REVENUE_PERCENT", "LEAD_ID", "PARTY_ID", "AL_SALES_METHODOLOGY_ID",
"SALES_METHODOLOGY_ID", "SALES_STAGE_ID", "PARTY_SITE_ID", "LOCATION_ID",
"SALES_CREDIT_ID", "LEAD_LINE_ID", "INVENTORY_ITEM_ID", "ORGANIZATION_ID",
"MSKFV_INVENTORY_ITEM_ID", "MSKFV_ORGANIZATION_ID", "HOU_ORGANIZATION_ID",
"COPYRIGHT") AS
SELECT AL.LEAD_NUMBER OPPERTUNITY_NUMBER ,
AL.DESCRIPTION OPPERTUNITY_NAME ,
HP.PARTY_NAME CUSTOMER ,
HL.ADDRESS1
||HL.ADDRESS2
||HL.ADDRESS3
||HL.ADDRESS4
||HL.CITY
||HL.COUNTY
||HL.STATE
||HL.COUNTRY ADDRESS ,
AL.DECISION_DATE CLOSED_DATE ,
ASMV.NAME METHODLOGY ,
ASSA.NAME SATGE ,
AL.STATUS STAUS ,
AL.CLOSE_REASON CLOSE_REASON ,
AL.WIN_PROBABILITY WIN_PROBABILITY ,
AL.CHANNEL_CODE SALES_CHANNEL ,
AL.TOTAL_AMOUNT AMOUNT ,
FCV.NAME CURRENCY ,
-- AMSC.ARC_SOURCE_CODE_FOR SOURCE_NAME ,
HOU.NAME OPERATING_UNIT
/* , DECODE
(amsc.arc_source_code_for ,
'CAMP',
( SELECT campaign_name
),
'EVEH',
( SELECT event_header_name
),
'EONE',
( SELECT event_offer_name
),
'EVEO',
),
'CSCH',
( SELECT schedule_name
),
'OFFR',
( SELECT description
WHERE list_header_id = amsc.source_code_for_id
),
NULL
) source_name*/
NVL(MSIT.DESCRIPTION,MCT.DESCRIPTION) PRODUCT ,
MSKFV.CONCATENATED_SEGMENTS ITEM_NAME ,
ASLL.QUANTITY QUANTITY ,
ASLL.UOM_CODE UOM ,
ASLL.TOTAL_AMOUNT TOTAL_AMOUNT ,
ASLL.FORECAST_DATE FORECAST_DATE ,
ASSC.OPP_WORST_FORECAST_AMOUNT WORST ,
ASSC.OPP_FORECAST_AMOUNT FORECAST ,
ASSC.OPP_BEST_FORECAST_AMOUNT BEST ,
ASSC.REVENUE_AMOUNT REVENUE_AMOUNT ,
ASSC.REVENUE_PERCENT REVENUE_PERCENT
--Primary Keys
AL.LEAD_ID LEAD_ID ,
HP.PARTY_ID PARTY_ID ,
-- AMSC.SOURCE_CODE_ID SOURCE_CODE_ID ,
AL.SALES_METHODOLOGY_ID AL_SALES_METHODOLOGY_ID ,
ASMV.SALES_METHODOLOGY_ID SALES_METHODOLOGY_ID ,
ASSA.SALES_STAGE_ID SALES_STAGE_ID ,
HPS.PARTY_SITE_ID PARTY_SITE_ID ,
HL.LOCATION_ID LOCATION_ID ,
ASSC.SALES_CREDIT_ID SALES_CREDIT_ID,
ASLL.LEAD_LINE_ID,
MSIT.INVENTORY_ITEM_ID,
MSIT.ORGANIZATION_ID,
MSKFV.INVENTORY_ITEM_ID MSKFV_INVENTORY_ITEM_ID,
MSKFV.ORGANIZATION_ID MSKFV_ORGANIZATION_ID,
HOU.ORGANIZATION_ID HOU_ORGANIZATION_ID

Copyright
--gl#accountff#end
FROM AS_LEADS AL,
AS_LEAD_LINES ASLL,
MTL_SYSTEM_ITEMS_VL MSIT,
MTL_CATEGORIES_TL MCT,
MTL_SYSTEM_ITEMS_B_KFV MSKFV,
HZ_PARTIES HP,
HZ_PARTY_SITES HPS,
HZ_LOCATIONS HL,
AS_SALES_METHODOLOGY_VL ASMV,
AS_SALES_STAGES_ALL_TL ASSA,
FND_CURRENCIES_VL FCV,
AS_SALES_CREDITS ASSC,
WHERE AL.LEAD_ID = ASLL.LEAD_ID
AND ASLL.INVENTORY_ITEM_ID = MSIT.INVENTORY_ITEM_ID(+)
AND ASLL.ORGANIZATION_ID = MSIT.ORGANIZATION_ID (+)

AND ASLL.PRODUCT_CATEGORY_ID = MCT.CATEGORY_ID
AND MSKFV.INVENTORY_ITEM_ID (+) = MSIT.INVENTORY_ITEM_ID
AND MSKFV.ORGANIZATION_ID (+) = MSIT.ORGANIZATION_ID
AND ASLL.LEAD_LINE_ID = ASSC.LEAD_LINE_ID
AND HP.PARTY_ID = AL.CUSTOMER_ID
AND AL.ADDRESS_ID = HPS.PARTY_SITE_ID(+)
AND AL.CUSTOMER_ID = HPS.PARTY_ID(+)
AND HPS.LOCATION_ID = HL.LOCATION_ID(+)
-- AND AL.SOURCE_PROMOTION_ID = AMSC.SOURCE_CODE_ID(+)
AND ASMV.SALES_METHODOLOGY_ID(+) = AL.SALES_METHODOLOGY_ID
AND ASSA.SALES_STAGE_ID (+) = AL.SALES_STAGE_ID
AND AL.CURRENCY_CODE = FCV.CURRENCY_CODE
AND AL.ORG_ID =HOU.ORGANIZATION_ID
;
CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_OPPORTUNITY_TASKS_V"
("OPPORTUNITY_NUMBER", "OPPORTUNITY", "CREATION_DATE", "OPERATING_UNIT", "CREATED_BY",
"CREATED_BY_ID", "CUSTOMER", "CLOSE_DATE", "STATUS", "STAGE", "SALES_CHANNEL", "AMOUNT",
"CURRENCY_CODE", "WIN_PROBABILITY", "CLOSE_REASON", "SALES_METHODOLOGY",
"POSTAL_CODE", "STATE", "COUNTRY", "TASK_ID", "TASK_NUMBER", "SUBJECT", "TYPE",
"TASK_TYPE_ID", "TAST_STATUS", "TASK_STATUS_ID", "DUE_DATE", "OWNER_ID", "OWNER",
"OWNER_TYPE_CODE", "OWNER_TYPE", "CUSTOMER_ID", "CUSTOMER_NAME",
"CUSTOMER_NUMBER", "PARTY_SITE_ID", "LOCATION_ID", "SALES_METHODOLOGY_ID",
"AMSC_SOURCE_CODE_ID", "SALES_STAGE_ID", "ORG_ID", "PARTY_ID", "ORGANIZATION_ID",
"OPPORTUNITY_ID", "FU_USER_ID", "COPYRIGHT") AS
AL.CREATION_DATE,
FU.CREATED_BY CREATED_BY_ID,
AL.STATUS STATUS,
ASSV.NAME STAGE,
AL.CHANNEL_CODE SALES_CHANNEL,
AL.TOTAL_AMOUNT AMOUNT,
AL.CURRENCY_CODE,
al.win_probability,
al.close_reason,
HL.ADDRESS1 ,
HL.ADDRESS2,
HL.ADDRESS3,
HL.ADDRESS4,
HL.CITY,
HL.POSTAL_CODE,
HL.STATE,
HL.COUNTRY,
-- ARC_SOURCE_CODE_FOR ,
/*,
DECODE (amsc.arc_source_code_for , 'CAMP',
), 'EVEH',
), 'EONE',
), 'EVEO',
), 'CSCH',
), 'OFFR',
(SELECT description
), NULL ) source, */
JTV.TASK_ID,
JTV.TASK_NUMBER,
JTV.TASK_TYPE TYPE,
JTV.TASK_TYPE_ID,
JTV.TASK_STATUS TAST_STATUS,
JTV.TASK_STATUS_ID,
JTV.OWNER_ID,
JTV.OWNER,
JTV.OWNER_TYPE,
JTV.CUSTOMER_ID,
JTV.CUSTOMER_NAME,
-----Primary Keys
HPS.PARTY_SITE_ID,
HL.LOCATION_ID,
AMSC.SOURCE_CODE_ID AMSC_SOURCE_CODE_ID,
ASSV.ORG_ID,
HP.PARTY_ID,
Copyright
--gl#accountff#end
FROM AS_LEADS AL,
FND_USER FU,
HZ_PARTIES HP,
HZ_PARTY_SITES HPS,
HZ_LOCATIONS HL,
AS_SALES_METHODOLOGY_VL ASML,
AMS_SOURCE_CODES AMSC,
JTF_TASKS_V JTV
WHERE 1 =1
AND AMSC.SOURCE_CODE_ID =AL.SOURCE_PROMOTION_ID
AND AL.LEAD_ID =JTV.SOURCE_OBJECT_ID
;
CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_OPPORTUNITY_V" ("OPPORTUNITY_NUMBER",
"OPPORTUNITY", "CREATION_DATE", "OPERATING_UNIT", "CREATED_BY", "CREATED_BY_ID",
"CUSTOMER", "CLOSE_DATE", "STATUS", "STAGE", "SALES_CHANNEL", "AMOUNT", "CURRENCY_CODE",
"WIN_PROBABILITY", "CLOSE_REASON", "SALES_METHODOLOGY",
"POSTAL_CODE", "STATE", "COUNTRY", "PARTY_ID", "OPPORTUNITY_ID", "PARTY_SITE_ID",
"LOCATION_ID", "SALES_METHODOLOGY_ID", "SALES_STAGE_ID", "ORG_ID", "ORGANIZATION_ID",
AL.CREATION_DATE,
FU.CREATED_BY CREATED_BY_ID,
AL.STATUS STATUS,
ASSV.NAME STAGE,
AL.CHANNEL_CODE SALES_CHANNEL,
AL.TOTAL_AMOUNT AMOUNT,
AL.CURRENCY_CODE,
al.win_probability,
al.close_reason,
HL.ADDRESS1 ,
HL.ADDRESS2,
HL.ADDRESS3,
HL.ADDRESS4,
HL.CITY,
HL.POSTAL_CODE,
HL.STATE,
HL.COUNTRY,
-- arc_source_code_for,
/*,
DECODE (amsc.arc_source_code_for , 'CAMP',
), 'EVEH',
), 'EONE',
), 'EVEO',
), 'CSCH',
), 'OFFR',
(SELECT description

), NULL ) source */
--Primary Keys
HP.PARTY_ID,
HPS.PARTY_SITE_ID,
HL.LOCATION_ID,
ASSV.ORG_ID,

Copyright
-- AMSC.SOURCE_CODE_ID
--gl#accountff#end
FROM AS_LEADS AL,
FND_USER FU,
HZ_PARTIES HP,
HZ_PARTY_SITES HPS,
HZ_LOCATIONS HL,
AS_SALES_METHODOLOGY_VL ASML
-- AMS_SOURCE_CODES AMSC
WHERE 1 =1

CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_PAYMENT_TRANS_V" ("PAYEE_SALESREP_ID",

"SALESREP", "POSTING_BATCH_ID", "POSTING_TYPE", "TRX_TYPE", "ROLE_ID",
"INCENTIVE_TYPE_CODE", "AMOUNT", "PROCESSED_DATE", "EVENT_FACTOR", "PAYMENT_FACTOR",
"QUOTA_FACTOR", "COMMISSION_RATE", "PAYMENT_AMOUNT", "PAYRUN_ID", "COMM_PTD",
"BONUS_PTD", "COMM_DRAW", "BONUS_DRAW", "COMM_PAID", "BONUS_PAID", "DRAW_PAID",
"REASON", "BONUS_REASON", "RECOVERY_METHOD", "DRAW_PTD", "BONUS_GIVEN",
"CPW_GUARANTEE", "ADJUST_PAID", "PAYMENT_WORKSHEET_ID", "PMT_AMOUNT_CALC",
"CURRENT_EARNINGS_DUE", "CURRENT_EARNINGS", "BB_PRIOR_PERIOD_ADJ",
"WORKSHEET_STATUS", "ORGANIZATION_NAME", "CREATION_DATE", "CREATED_BY_ID",
"CREATED_BY", "SALESREP_NUMBER", "RS_SALESREP_ID", "PAYMENT_TRANSACTION_ID", "ORG_ID",
"RS_ORG_ID", "ORGANIZATION_ID", "FU_USER_ID", "COPYRIGHT") AS
SELECT
CPT.PAYEE_SALESREP_ID,
RS.NAME SALESREP,
CPT.POSTING_BATCH_ID,
CPT.POSTING_TYPE,
CPT.TRX_TYPE,
CPT.ROLE_ID,
CPT.INCENTIVE_TYPE_CODE,
CPT.AMOUNT,
CPT.PROCESSED_DATE,
CPT.EVENT_FACTOR,
CPT.PAYMENT_FACTOR,
CPT.QUOTA_FACTOR,
CPT.COMMISSION_RATE,
CPT.PAYMENT_AMOUNT,
CPT.PAYRUN_ID,
CPW.COMM_PTD,
CPW.BONUS_PTD,
CPW.COMM_DRAW,
CPW.BONUS_DRAW,
CPW.COMM_PAID,
CPW.BONUS_PAID,
CPW.DRAW_PAID,
CPW.REASON,
CPW.BONUS_REASON,
CPW.RECOVERY_METHOD,
CPW.DRAW_PTD,
CPW.BONUS_GIVEN,
CPW.GUARANTEE CPW_GUARANTEE,
CPW.ADJUST_PAID,
CPW.PAYMENT_WORKSHEET_ID,
CPW.PMT_AMOUNT_CALC,
CPW.CURRENT_EARNINGS_DUE,
CPW.CURRENT_EARNINGS,
CPW.BB_PRIOR_PERIOD_ADJ,
CPW.WORKSHEET_STATUS,
OOD.ORGANIZATION_NAME ,
CPT.CREATION_DATE,
CPT.CREATED_BY CREATED_BY_ID,
RS.SALESREP_NUMBER,
--PRIMARY KEYS
RS.SALESREP_ID RS_SALESREP_ID,
CPT.PAYMENT_TRANSACTION_ID,
CPT.ORG_ID,
RS.ORG_ID RS_ORG_ID,

Copyright
--gl#accountff#end
FROM CN_PAYMENT_TRANSACTIONS CPT,
RA_SALESREPS RS,
CN_PAYMENT_WORKSHEETS CPW,
FND_USER FU
WHERE 1 =1
AND CPT.PAYEE_SALESREP_ID=RS.SALESREP_ID
AND CPT.payrun_ID =CPW.PAYRUN_ID
AND CPT.ORG_ID =OOD.ORGANIZATION_ID
AND CPT.CREATED_BY =FU.USER_ID
;
CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_PROPOSALS_TASKS_V" ("PROPOSAL_NAME",
"PROPOSAL_DESC", "PROPOSAL_STATUS", "DUE_DATE", "CUSTOMER_NAME", "CUSTOMER_CONTACT",
"TEMPLATE_NAME", "TEMPLATE_DESC", "CREATION_DATE", "CREATED_BY_ID", "CREATED_BY",
"TASK_DUE_DATE", "TASK_NUMBER", "SUBJECT", "TYPE", "TASK_TYPE_ID", "TASK_STATUS",
"TASK_STATUS_ID", "OWNER_ID", "OWNER", "OWNER_TYPE_CODE", "OWNER_TYPE", "PROPOSAL_ID",
"PARTY_ID", "CONTACT_ID", "TEMPLATE_ID", "TASK_ID", "FU_USER_ID", "COPYRIGHT") AS
SELECT PP.PROPOSAL_NAME,
PP.PROPOSAL_DESC,
PP.PROPOSAL_STATUS,
PP.DUE_DATE,
HP.PARTY_NAME CUSTOMER_NAME,
HP1.PARTY_NAME CUSTOMER_CONTACT,
PTV.TEMPLATE_NAME,
PTV.TEMPLATE_DESC,
PP.CREATION_DATE,
PP.CREATED_BY CREATED_BY_ID,
JTV.SCHEDULED_END_DATE TASK_DUE_DATE,
JTV.TASK_NUMBER,
JTV.TASK_TYPE TYPE,
JTV.TASK_TYPE_ID,
JTV.TASK_STATUS_ID,
JTV.OWNER_ID,
JTV.OWNER,
JTV.OWNER_TYPE,
---Primary Keys
PP.PROPOSAL_ID,
HP.PARTY_ID,
HP1.PARTY_ID CONTACT_ID,
PTV.TEMPLATE_ID,
JTV.TASK_ID,

Copyright
--gl#accountff#end
FROM PRP_PROPOSALS PP,
HZ_PARTIES HP,
HZ_PARTIES HP1,
PRP_TEMPLATES_VL PTV,
FND_USER FU,
JTF_TASKS_V JTV
WHERE 1 =1
AND PP.PARTY_ID =HP.PARTY_ID
AND PP.CONTACT_PARTY_ID=HP1.PARTY_ID
AND PP.TEMPLATE_ID =PTV.TEMPLATE_ID
AND PP.CREATED_BY =FU.USER_ID
AND PP.PROPOSAL_ID =JTV.SOURCE_OBJECT_ID
;
CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_PROPOSALS_V" ("PROPOSAL_NAME",
"PROPOSAL_DESC", "PROPOSAL_STATUS", "DUE_DATE", "CUSTOMER_NAME", "CUSTOMER_CONTACT",
"TEMPLATE_NAME", "TEMPLATE_DESC", "CREATION_DATE", "CREATED_BY_ID", "CREATED_BY",
"PROPOSAL_ID", "PARTY_ID", "CONTACT_ID", "TEMPLATE_ID", "FU_USER_ID", "COPYRIGHT") AS
SELECT PP.PROPOSAL_NAME,
PP.PROPOSAL_DESC,
PP.PROPOSAL_STATUS,
PP.DUE_DATE,
HP.PARTY_NAME CUSTOMER_NAME,
HP1.PARTY_NAME CUSTOMER_CONTACT,
PTV.TEMPLATE_NAME,
PTV.TEMPLATE_DESC,
PP.CREATION_DATE,
PP.CREATED_BY CREATED_BY_ID,
---Primary Keys
PP.PROPOSAL_ID,
HP.PARTY_ID,
HP1.PARTY_ID CONTACT_ID,
PTV.TEMPLATE_ID,

Copyright
--gl#accountff#end
FROM PRP_PROPOSALS PP,
HZ_PARTIES HP,
HZ_PARTIES HP1,
PRP_TEMPLATES_VL PTV,
FND_USER FU
WHERE 1 =1
AND PP.PARTY_ID =HP.PARTY_ID
AND PP.CONTACT_PARTY_ID=HP1.PARTY_ID
AND PP.TEMPLATE_ID =PTV.TEMPLATE_ID
AND PP.CREATED_BY =FU.USER_ID
CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_QUOTE_DETAILS_V" ("OPPORTUNITY_NAME",

"QUOTE_NAME", "SALES_ORDER_NUMBER", "ORG_ID", "QUOTE_SOURCE_CODE", "CUSTOMER",
"ACCOUNT_NUMBER", "CONTACT", "CHANNEL_CODE", "AMOUNT", "EXPIRATION_DATE",
"SALES_GROUP", "CURRENCY", "PRICE_LIST", "AGREEMENT", "ORDER_TYPE", "OPERATING_UNIT",
"SALES_PERSON_NAME", "RESELLER", "RESELLER_CONTACT", "RESELLER_PHONE",
"RESELLER_ADDRESS", "CONSULTING_PARTNER", "CONSULTING_PARTNER_CONTACT",
"CONSULTING_PARTNER_PHONE", "CONSULTING_PARTNER_ADDRESS", "PRICE_LIST_ID",
"ORDER_TYPE_ID", "QUOTE_HEADER_ID", "ORDER_ID", "LEAD_ID", "PARTY_ID", "RELATED_OBJECT_ID",
"QUOTE_VERSION", "QUOTE_NUMBER", "CUST_ACCOUNT_ID", "LEAD_OPPORTUNITY_ID",
"LEAD_CONTACT_ID", "HEADER_ID", "REP_ORG_ID", "SALES_CREDIT_ID", "GROUP_ID",
"LIST_HEADER_ID", "AGREEMENT_ID", "TRANSACTION_TYPE_ID", "ORGANIZATION_ID",
"SALES_LEAD_ID", "SALESREP_ID", "FCTL_CURRENCY_CODE", "COPYRIGHT") AS
AQH.QUOTE_NAME QUOTE_NAME,
OOH.ORDER_NUMBER SALES_ORDER_NUMBER,
AQH.ORG_ID ORG_ID,
AQH.QUOTE_SOURCE_CODE QUOTE_SOURCE_CODE,
HZ.PARTY_NAME CUSTOMER ,
HCA.ACCOUNT_NUMBER ACCOUNT_NUMBER ,
-- AMSC.ARC_SOURCE_CODE_FOR SOURCE_NAME,
/* decode (amsc.arc_source_code_for ,
'CAMP',
),
'EVEH',
),
'EONE',
),
'EVEO',
),
'CSCH',
),
'OFFR',

),
null
)source_name,*/
SELECT NVL(HPP.PERSON_NAME,NULL)
FROM HZ_ORG_CONTACTS_CPUI_V HOC,
HZ_PERSON_PROFILES_CPUI_V HPP
WHERE ASLC.CONTACT_PARTY_ID = HOC.RELATIONSHIP_PARTY_ID
AND HOC.OBJECT_ID = ASLC.CUSTOMER_ID
AND HOC.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HOC.SUBJECT_ID = HPP.PARTY_ID
AND HOC.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
)CONTACT,
AQH.SALES_CHANNEL_CODE CHANNEL_CODE,
AQH.TOTAL_QUOTE_PRICE AMOUNT,
AQH.QUOTE_EXPIRATION_DATE EXPIRATION_DATE,
ASGV.name SALES_GROUP,
FCTL.name CURRENCY,
QPLV.name PRICE_LIST,
OAV.name AGREEMENT,
OTT.name ORDER_TYPE,
HOU.name OPERATING_UNIT,
REP.name SALES_PERSON_NAME,
AQH.ATTRIBUTE1 RESELLER,
AQH.ATTRIBUTE2 RESELLER_CONTACT,
AQH.ATTRIBUTE3 RESELLER_PHONE,
AQH.ATTRIBUTE4 RESELLER_ADDRESS,
AQH.ATTRIBUTE5 CONSULTING_PARTNER,
AQH.ATTRIBUTE6 CONSULTING_PARTNER_CONTACT,
AQH.ATTRIBUTE7 CONSULTING_PARTNER_PHONE,
AQH.ATTRIBUTE8 CONSULTING_PARTNER_ADDRESS,
AQH.PRICE_LIST_ID PRICE_LIST_ID,
OOH.ORDER_TYPE_ID ORDER_TYPE_ID,
AQH.QUOTE_HEADER_ID QUOTE_HEADER_ID,
AQH.ORDER_ID ORDER_ID,
--PRIMARY KEYS
AL.LEAD_ID ,
HZ.PARTY_ID PARTY_ID,
AQRO.RELATED_OBJECT_ID,
AQH.QUOTE_VERSION,
AQH.QUOTE_NUMBER QUOTE_NUMBER,
-- AMSC.SOURCE_CODE_ID,
HCA.CUST_ACCOUNT_ID,
ASLP.LEAD_OPPORTUNITY_ID,
ASLC.LEAD_CONTACT_ID,
OOH.HEADER_ID,
REP.ORG_ID REP_ORG_ID,
ASSC.SALES_CREDIT_ID,
ASGV.SALES_GROUP_ID GROUP_ID,
QPLV.PRICE_LIST_ID LIST_HEADER_ID,
OAV.AGREEMENT_ID,
OTT.TRANSACTION_TYPE_ID,
ASL.SALES_LEAD_ID SALES_LEAD_ID,
REP.SALESREP_ID SALESREP_ID,
FCTL.CURRENCY_CODE FCTL_CURRENCY_CODE

Copyright
--gl#accountff#end
FROM AS_LEADS AL,
ASO_QUOTE_RELATED_OBJECTS AQRO,
ASO_QUOTE_HEADERS AQH,
HZ_PARTIES HZ,
HZ_CUST_ACCOUNTS HCA,
AS_SALES_LEADS ASL,
AS_SALES_LEAD_OPPORTUNITY ASLP,
AS_SALES_LEAD_CONTACTS ASLC,
OE_ORDER_HEADERS OOH,
RA_SALESREPS REP,
AS_SALES_CREDITS ASSC,
AS_SALES_GROUPS_V ASGV,
FND_CURRENCIES_VL FCTL,
QP_PRICE_LISTS_V QPLV,
OE_AGREEMENTS_VL OAV,
OE_TRANSACTION_TYPES_VL OTT,
WHERE AQRO.QUOTE_OBJECT_TYPE_CODE = 'HEADER'
AND AL.LEAD_ID = AQRO.OBJECT_ID(+)
AND AQH.QUOTE_HEADER_ID = AQRO.QUOTE_OBJECT_ID
AND AL.LEAD_ID = ASLP.OPPORTUNITY_ID
--- AND AL.SOURCE_PROMOTION_ID = AMSC.SOURCE_CODE_ID(+)
AND ASLP.SALES_LEAD_ID(+) = ASL.SALES_LEAD_ID
AND HZ.PARTY_ID (+) = ASL.CUSTOMER_ID

AND HZ.PARTY_ID = HCA.PARTY_ID
AND HCA.CUST_ACCOUNT_ID = AQH.CUST_ACCOUNT_ID
AND ASL.SALES_LEAD_ID = ASLC.SALES_LEAD_ID(+)
AND OOH.HEADER_ID = AQH.ORDER_ID
AND OOH.SALESREP_ID = REP.SALESREP_ID(+)
AND AL.LEAD_ID = ASSC.LEAD_ID(+)
AND ASGV.SALES_GROUP_ID(+) = ASSC.SALESGROUP_ID
and ASGV.manager_person_id(+) = ASSC.person_id
AND FCTL.CURRENCY_CODE = AQH. CURRENCY_CODE(+)
AND AQH.PRICE_LIST_ID = QPLV.PRICE_LIST_ID(+)
AND AQH.CONTRACT_ID = OAV.AGREEMENT_ID(+)
AND OOH.ORDER_TYPE_ID = OTT.TRANSACTION_TYPE_ID(+)
AND AQH.ORG_ID = HOU.ORGANIZATION_ID(+)
CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_SALES_CUSTOMERS_V" ("PARTY_NUMBER",

"NAME", "CREATION_DATE", "CREATED_BY_ID", "PARTY_TYPE", "D_U_N_S_NUMBER", "WEB_SITE",
"EMAIL_ADDRESS", "ACCOUNT_NUMBER", "PARTY_SITE_NUMBER", "COUNTRY", "ADDRESS_LINE1",
"ADDRESS_LINE2", "ADDRESS_LINE3", "ADDRESS_LINE4", "CITY", "COUNTY", "STATE", "PROVINCE",
"POSTAL_CODE", "PURPOSE", "STATUS", "PARTY_ID", "CUST_ACCOUNT_ID", "PARTY_SITE_ID",
"LOCATION_ID", "CUST_ACCT_SITE_ID", "SITE_USE_ID", "FU_USER_ID", "COPYRIGHT") AS
SELECT HP.PARTY_NUMBER PARTY_NUMBER,
HP.PARTY_NAME NAME,
hp.creation_date creation_date,
hp.created_by created_by_id,
HP.PARTY_TYPE PARTY_TYPE,
HP.DUNS_NUMBER D_U_N_S_NUMBER,
HP.URL WEB_SITE,
HP.EMAIL_ADDRESS EMAIL_ADDRESS,
HCA.ACCOUNT_NUMBER ACCOUNT_NUMBER,
HPS.PARTY_SITE_NUMBER,
HL.COUNTRY,
HL.ADDRESS1 ADDRESS_LINE1,
HL.CITY,
HL.COUNTY,
HL.STATE,
HL.PROVINCE,
HL.POSTAL_CODE,
HCSUA.SITE_USE_CODE PURPOSE,
DECODE(HCSUA.STATUS,'A','ACTIVE','NOT ACTIVE') STATUS,
---Primary Keys
HP.PARTY_ID PARTY_ID,
HCA.CUST_ACCOUNT_ID CUST_ACCOUNT_ID,
HPS.PARTY_SITE_ID,
HL.LOCATION_ID,
HCASA.CUST_ACCT_SITE_ID,
HCSUA.SITE_USE_ID,

Copyright
--gl#accountff#end
FROM HZ_PARTIES HP,

HZ_CUST_ACCOUNTS HCA,
HZ_PARTY_SITES HPS,
HZ_LOCATIONS HL,
HZ_CUST_ACCT_SITES_ALL HCASA,
HZ_CUST_SITE_USES_ALL HCSUA,
FND_USER FU
WHERE 1 =1
AND HP.PARTY_ID =HCA.PARTY_ID
AND HCA.PARTY_ID =HPS.PARTY_ID
AND HPS.PARTY_SITE_ID =HCASA.PARTY_SITE_ID
AND HCASA.CUST_ACCT_SITE_ID=HCSUA.CUST_ACCT_SITE_ID
AND HP.CREATED_BY =FU.USER_ID
CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_TASKS_V" ("TASK_NUMBER", "SUBJECT", "TYPE",

"TASK_TYPE_ID", "TASK_STATUS", "TASK_STATUS_ID", "DUE_DATE", "OWNER_ID", "OWNER",
"OWNER_TYPE_CODE", "OWNER_TYPE", "CUSTOMER_ID", "CUSTOMER_NAME",
"CUSTOMER_NUMBER", "CREATED_BY", "CREATED_BY_ID", "CREATION_DATE", "TASK_ID",
SELECT JTV.TASK_NUMBER,
JTV.TASK_TYPE TYPE,
JTV.TASK_TYPE_ID,
JTV.TASK_STATUS_ID,
JTV.OWNER_ID,
JTV.OWNER,
JTV.OWNER_TYPE,
JTV.CUSTOMER_ID,
JTV.CUSTOMER_NAME,
JTV.CREATED_BY CREATED_BY_ID,
JTV.CREATION_DATE CREATION_DATE,
---Primary Keys
JTV.TASK_ID,

Copyright
--gl#accountff#end
FROM FND_USER FU,
JTF_TASKS_V JTV
WHERE 1 =1
AND JTV.CREATED_BY =FU.USER_ID
CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_TOP_TEN_OPPORTUNITY_V"

("OPPORTUNITY_NAME", "OPPORTUNITY_NUMBER", "CREATION_DATE", "CREATED_BY_ID",
"CREATED_BY", "CUSTOMER", "PARTY_TYPE", "CUSTOMER_ID", "PARTY_NUMBER", "STATUS",
"SALES_CHANNEL", "SOURCE_PROMOTION_ID", "CYCLE", "CYCLE_DESCRIPTION", "TOTAL_AMOUNT",
"CURRENCY_CODE", "DECISION_TIMEFRAME_CODE", "PRODUCT_CATEGORY_ID",
"PRODUCT_CAT_SET_ID", "PRODUCT_CATEGORY", "PRODUCT", "PRODUCT_UOM", "QUANTITY",
"PRODUCT_AMOUNT", "EMPLOYEES_TOTAL", "LEAD_ID", "FU_USER_ID", "PARTY_ID",
"SALES_CHANNEL_CODE", "SALES_STAGE_ID", "LEAD_LINE_ID", "CATEGORY_ID",
"INVENTORY_ITEM_ID", "ORGANIZATION_ID", "UOM_CODE", "ASSAT_LANGUAGE",
AL.CREATION_DATE,
HP.PARTY_TYPE,
AL.CUSTOMER_ID,
HP.PARTY_NUMBER,
AL.STATUS STATUS,
ASSAT.NAME CYCLE,
AL.TOTAL_AMOUNT,
AL.CURRENCY_CODE,
ALLA.QUANTITY,
HP.EMPLOYEES_TOTAL,
-----PRIMARY KEYS
AL.LEAD_ID LEAD_ID,
HP.PARTY_ID,
ALLA.LEAD_LINE_ID ,
MC.CATEGORY_ID,
MUOMT.UOM_CODE,
ASSAT.LANGUAGE ASSAT_LANGUAGE,

Copyright
--GL#ACCOUNTFF#END
FROM AS_LEADS AL,
FND_USER FU,
HZ_PARTIES HP,
MTL_CATEGORIES MC,

AND MUOMT.LANGUAGE(+) = USERENV('LANG')
ORDER BY NVL(AL.TOTAL_AMOUNT,0) DESC
CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_WIN_OPPERTUNITY_V"

("OPPERTUNITY_NUMBER", "OPPERTUNITY_NAME", "CUSTOMER", "ADDRESS", "CLOSED_DATE",
"METHODLOGY", "SATGE", "STAUS", "CLOSE_REASON", "WIN_PROBABILITY", "SALES_CHANNEL",
"AMOUNT", "CURRENCY", "OPERATING_UNIT", "LEAD_ID", "PARTY_ID",
"AL_SALES_METHODOLOGY_ID", "SALES_METHODOLOGY_ID", "SALES_STAGE_ID", "PARTY_SITE_ID",
"LOCATION_ID", "ORGANIZATION_ID", "FCV_CURRENCY_CODE", "ASSA_LANGUAGE", "COPYRIGHT") AS
SELECT AL.LEAD_NUMBER OPPERTUNITY_NUMBER ,
AL.DESCRIPTION OPPERTUNITY_NAME ,
HP.PARTY_NAME CUSTOMER ,
HL.ADDRESS1
||HL.ADDRESS2
||HL.ADDRESS3
||HL.ADDRESS4
||HL.CITY
||HL.COUNTY
||HL.STATE
||HL.COUNTRY ADDRESS ,
AL.DECISION_DATE CLOSED_DATE ,
ASMV.name METHODLOGY ,
ASSA.name SATGE ,
AL.STATUS STAUS ,
AL.CLOSE_REASON CLOSE_REASON ,
AL.WIN_PROBABILITY WIN_PROBABILITY ,
AL.CHANNEL_CODE SALES_CHANNEL ,
AL.TOTAL_AMOUNT AMOUNT ,
FCV.NAME CURRENCY ,
-- AMSC.ARC_SOURCE_CODE_FOR SOURCE_NAME ,
HOU.name OPERATING_UNIT
/*, decode
(amsc.arc_source_code_for ,
'CAMP',
),
'EVEH',
),
'EONE',
),
'EVEO',
),
'CSCH',
),
'OFFR',
),
NULL
) source_name*/
--Primary Keys
AL.LEAD_ID LEAD_ID ,
HP.PARTY_ID PARTY_ID ,
--- AMSC.SOURCE_CODE_ID SOURCE_CODE_ID ,
AL.SALES_METHODOLOGY_ID AL_SALES_METHODOLOGY_ID ,
ASMV.SALES_METHODOLOGY_ID SALES_METHODOLOGY_ID,
ASSA.SALES_STAGE_ID SALES_STAGE_ID ,
HPS.PARTY_SITE_ID PARTY_SITE_ID ,
HL.LOCATION_ID LOCATION_ID,
FCV.CURRENCY_CODE FCV_CURRENCY_CODE,
ASSA.LANGUAGE ASSA_LANGUAGE

Copyright
--gl#accountff#end
FROM AS_LEADS AL,
HZ_PARTIES HP,
HZ_PARTY_SITES HPS,
HZ_LOCATIONS HL,
AS_SALES_METHODOLOGY_VL ASMV,
AS_SALES_STAGES_ALL_TL ASSA,
FND_CURRENCIES_VL FCV,
WHERE HP.PARTY_ID = AL.CUSTOMER_ID
AND AL.ADDRESS_ID = HPS.PARTY_SITE_ID(+)
AND AL.CUSTOMER_ID = HPS.PARTY_ID(+)
AND HPS.LOCATION_ID = HL.LOCATION_ID(+)
-- AND AL.SOURCE_PROMOTION_ID = AMSC.SOURCE_CODE_ID(+)
AND ASMV.SALES_METHODOLOGY_ID(+) = AL.SALES_METHODOLOGY_ID
AND ASSA.SALES_STAGE_ID (+) = AL.SALES_STAGE_ID
AND ASSA.LANGUAGE = USERENV('LANG')
AND AL.CURRENCY_CODE = FCV.CURRENCY_CODE
AND AL.ORG_ID = HOU.ORGANIZATION_ID
AND AL.STATUS ='WON'

ASN Appointment

Uploaded by

Copyright:

Available Formats

ASN Appointment

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

ASN Appointment

Uploaded by

Copyright:

Available Formats

ASN Appointment

CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_APPOINTMENT_V" ("TASK_NUMBER", "SUBJECT",

, 'Copyright(c) 2001-'||to_char(SYSDATE,'YYYY')||' '||'EiS Technologies Inc. All rights reserved.'

FROM FND_USER FU,

AND JTV.CREATED_BY =FU.USER_ID

AND JTV.TASK_ID =CVCDV.TASK_ID

CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_COMM_PLAN_ELEMENTS_V"

, 'Copyright(c) 2001-'||to_char(SYSDATE,'YYYY')||' '||'EiS Technologies Inc. All rights reserved.'

from CN_PAYMENT_TRANSACTIONS CPT,

where CPT.QUOTA_ID = CQ.QUOTA_ID(+)

and CPT.CREDITED_SALESREP_ID = CS.SALESREP_ID

and CPT.PAYRUN_ID = CP.PAYRUN_ID

and HOU.ORGANIZATION_ID = CS.ORG_ID (+)

select V.OPERATING_UNIT OPERATING_UNIT,

V.BEGIN_BALANCE +V.EARNINGS+V.REC_DRAW_PD+V.NREC_DRAW_PD PAYMENT_DUE,

, 'Copyright(c) 2001-'||to_char(SYSDATE,'YYYY')||' '||'EiS Technologies Inc. All rights reserved.'

AND PAYRUN_ID =WK.PAYRUN_ID

AND INCENTIVE_TYPE_CODE IN( 'BONUS','COMMISSION')

AND PAY_PERIOD_ID <

(SELECT PAY_PERIOD_ID FROM CN_PAYRUNS WHERE PAYRUN_ID=WK.PAYRUN_ID

AND PAYRUN_ID =WK.PAYRUN_ID

AND INCENTIVE_TYPE_CODE = 'PMTPLN_REC'

AND PAYRUN_ID =WK.PAYRUN_ID

AND INCENTIVE_TYPE_CODE IN( 'BONUS','COMMISSION')

(SELECT PAY_PERIOD_ID FROM CN_PAYRUNS WHERE PAYRUN_ID=WK.PAYRUN_ID

AND INCENTIVE_TYPE_CODE = 'MANUAL_PAY_ADJ'

AND RECOVERABLE_FLAG ='Y'

(SELECT ( NVL(SUM(PAYMENT_AMOUNT),0) - NVL(SUM(AMOUNT),0))

AND PAYRUN_ID =WK.PAYRUN_ID

AND INCENTIVE_TYPE_CODE IN( 'BONUS','COMMISSION')

AND PAYRUN_ID =WK.PAYRUN_ID

AND INCENTIVE_TYPE_CODE = 'MANUAL_PAY_ADJ'

AND RECOVERABLE_FLAG ='N'

(SELECT NVL(-1 *SUM(PAYMENT_AMOUNT),0)

AND PAYRUN_ID =WK.PAYRUN_ID

AND INCENTIVE_TYPE_CODE = 'PMTPLN_REC'

AND WAIVE_FLAG ='Y'

FROM CN_PAYMENT_WORKSHEETS WK,

and WK.PAYRUN_ID =CP.PAYRUN_ID

--AND WK.SALESREP_ID = 100000004

-- AND WK.PAYRUN_ID =2654

and WK.QUOTA_ID(+) is null

SELECT AL.DESCRIPTION OPPORTUNITY_NAME,

WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) BETWEEN 1 AND 9

WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) BETWEEN 10 AND 29

WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) BETWEEN 30 AND 59

WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) BETWEEN 60 AND 89

WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) BETWEEN 90 AND 179

WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) BETWEEN 180 AND 365

WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) >365

, 'Copyright(c) 2001-'||to_char(SYSDATE,'YYYY')||' '||'EiS Technologies Inc. All rights reserved.'

FROM AS_LEADS AL,

WHERE AL.CREATED_BY =FU.USER_ID

AND AL.CUSTOMER_ID =HP.PARTY_ID

AND AL.CHANNEL_CODE = ASO_CHANNEL.SALES_CHANNEL_CODE(+)

AND AL.SALES_STAGE_ID =ASSAT.SALES_STAGE_ID(+)

AND ASSAT.LANGUAGE(+) = USERENV('LANG')

AND AL.LEAD_ID =ALLA.LEAD_ID(+)

AND ALLA.INVENTORY_ITEM_ID =MSIK.INVENTORY_ITEM_ID(+)