steps for reverse-commission

STEPS FOR REVERSE-COMMISSION PROCESS:

1. Update COLUMN_2 Flag as null in CICMS_AR_INV_LINE_INT_HIS for BATCH_RUN_DATE between Date Range of Commission Calculation Pay-Scale.

UPDATE CICMS_AR_INV_LINE_INT_HIS SET COLUMN_2=NULLWHERE TRUNC(BATCH_RUN_DATE) BETWEEN '01-OCT-2012' AND '25-OCT-2012';

2. Delete CICMS_COMMISSION_CALC_STG for specified BATCH_RUN_DATE or IMPORT_REQUEST_ID.

DELETE FROM CICMS_COMMISSION_CALC_STGWHERE BATCH_RUN_DATE BETWEEN ’01-OCT-2012’ and ’25-OCT-2012’

3. Delete CICMS_COMMISSION_CALC_INFO for specified BATCH_RUN_DATE or BATCH_RUN_CONC_REQ_ID.

DELETE FROM CICMS_COMMISSION_CALC_INFOWHERE BATCH_RUN_DATE=’29-OCT-2012’

If Policies are not captured in CICMS_AR_INV_STG_HIS and CICMS_AR_INV_LINE_INT_HIS then insert the policies from APPS.XX_MAXIMUS_ARINV_STG_HIS and APPS.XX_AR_INV_LINE_PRE_INT_HIS respectively.The package which transfers the data from Beacon to CICMS is XX_AR_INV_CONV_PKG.

Query to insert data from APPS.XX_MAXIMUS_ARINV_STG_HIS to CICMS_AR_INV_STG_HIS

INSERT INTO

CICMS_AR_INV_STG_HIS

(RECORD_ID,

DOC_NO,

1

INVOICE_NO,

TRANSACTION_TYPE,

DESCRIPTION,

INVOICE_DUE_DATE,

DOC_CREATION_DATE,

AMOUNT,

MODE_OF_PAYMENT,

DOC_TYPE,

GL_CODE,

STATUS,

SETTLED_BY,

REVERSAL_FLAG,

MAX_BUPA_BRANCH_CODE,

MAX_BUPA_BRANCH_LOC,

BRANCH_CODE,

BRANCH_DESCRIPTION,

CUST_ID,

PIVOTAL_CUST_ID,

POLICY_PROPOSAL,

CUST_NAME,

COMM_ADDR_LINE1,

COMM_ADDR_LINE2,

COMM_ADDR_LINE3,

COMM_ADDR_LINE4,

COMM_ADDR_PINCODE,

COMM_ADDR_DISTRICT,

COMM_ADDR_CITY,

2

COMM_ADDR_STATE,

AGENT_ID,

AGENT_NAME,

SALES_PERSON_CODE,

SALES_PERSON_NAME,

CHANNEL,

PLAN_ID,

PLAN_DESCRIPTION,

CONTRACT_EFF_DATE,

CONTRACT_TERM_DATE,

TERMINATION_REASON,

ACCOUNT_BALANCE,

BATCH_RUN_DATE,

PRODUCT_ID,

BATCH_ID,

CLAIM_NO_PREAUTH_ID,

PREAUTH_ID_FROM_CLAIM,

MAIN_CLAIM_NO,

REQUESTED_AMOUNT,

ADMISSION_DATE,

DISCHARGE_DATE,

CLAIM_PAYMENT_METHOD,

BANK_OF_CUST,

BRANCH_NO_OF_CUST,

BRANCH_NAME_OF_CUST,

BANK_LOCATION_OF_CUST,

IFSC_CODE_OF_CUST,

3

MICR_CODE_OF_CUST,

ACCOUNT_NO_OF_CUST,

PAYEE_NAME_OF_CUST,

ACCOUNT_NO_OF_PRO,

PAYEE_NAME_OF_PRO,

PRO_COMM_ADDR_LINE1,




PRO_COMM_ADDR_PINCODE,

PRO_COMM_ADDR_DIST,

PRO_COMM_ADDR_CITY,

PRO_COMM_ADDR_STATE,

PREAUTH_APPROVED_AMOUNT,

CREATION_DATE,

PLAN_LIMIT,

GL_COMPANY,

GL_LINE_OF_BUSINESS,

GL_CHANNEL,

GL_PRODUCT,

GL_LOCATION,

GL_DEPARTMENT,

GL_SUB_ACCOUNT,

GL_EXPENSE_TYPE,

GL_FUTURE1,

GL_FUTURE2,

REQUEST_ID,

4

VALIDATION_RECORD_FLAG,

ERROR_MESSAGE,

CREATED_BY,

CREATIED_DATE,

LAST_UPDATED_BY,

LAST_UPDATE_DATE,

LAST_UPDATE_LOGIN,

NOL,

APPLICATION_FORM_ID)

SELECT

RECORD_ID,

DOC_NO,

INVOICE_NO,

TRANSACTION_TYPE,

DESCRIPTION,

INVOICE_DUE_DATE,

DOC_CREATION_DATE,

AMOUNT,

MODE_OF_PAYMENT,

DOC_TYPE,

GL_CODE,

STATUS,

SETTLED_BY,

REVERSAL_FLAG,

MAX_BUPA_BRANCH_CODE,

MAX_BUPA_BRANCH_LOC,

5

BRANCH_CODE,

BRANCH_DESCRIPTION,

CUST_ID,

PIVOTAL_CUST_ID,

POLICY_PROPOSAL,

CUST_NAME,

COMM_ADDR_LINE1,

COMM_ADDR_LINE2,

COMM_ADDR_LINE3,

COMM_ADDR_LINE4,

COMM_ADDR_PINCODE,

COMM_ADDR_DISTRICT,

COMM_ADDR_CITY,

COMM_ADDR_STATE,

AGENT_ID,

AGENT_NAME,

SALES_PERSON_CODE,

SALES_PERSON_NAME,

CHANNEL,

PLAN_ID,

PLAN_DESCRIPTION,

CONTRACT_EFF_DATE,

CONTRACT_TERM_DATE,

TERMINATION_REASON,

ACCOUNT_BALANCE,

BATCH_RUN_DATE,

PRODUCT_ID,

6

BATCH_ID,

CLAIM_NO_PREAUTH_ID,

PREAUTH_ID_FROM_CLAIM,

MAIN_CLAIM_NO,

REQUESTED_AMOUNT,

ADMISSION_DATE,

DISCHARGE_DATE,

CLAIM_PAYMENT_METHOD,

BANK_OF_CUST,

BRANCH_NO_OF_CUST,

BRANCH_NAME_OF_CUST,

BANK_LOCATION_OF_CUST,

IFSC_CODE_OF_CUST,

MICR_CODE_OF_CUST,

ACCOUNT_NO_OF_CUST,

PAYEE_NAME_OF_CUST,

ACCOUNT_NO_OF_PRO,

PAYEE_NAME_OF_PRO,





PRO_COMM_ADDR_PINCODE,

PRO_COMM_ADDR_DIST,

PRO_COMM_ADDR_CITY,

PRO_COMM_ADDR_STATE,

PREAUTH_APPROVED_AMOUNT,

7

CREATION_DATE,

PLAN_LIMIT,

GL_COMPANY,

GL_LINE_OF_BUSINESS,

GL_CHANNEL,

GL_PRODUCT,

GL_LOCATION,

GL_DEPARTMENT,

GL_SUB_ACCOUNT,

GL_EXPENSE_TYPE,

GL_FUTURE1,

GL_FUTURE2,

REQUEST_ID,


ERROR_MESSAGE,

CREATED_BY,

CREATIED_DATE,

LAST_UPDATED_BY,

LAST_UPDATE_DATE,

LAST_UPDATE_LOGIN,

NOL,

APPLICATION_FORM_ID

FROM APPS.XX_MAXIMUS_ARINV_STG_HIS

WHERE BATCH_RUN_DATE BETWEEN '17-OCT-2012' AND '25-OCT-2012';

Query to insert data from APPS.XX_AR_INV_LINE_PRE_INT_HIS to

CICMS_AR_INV_LINE_INT_HIS.

8

INSERT INTO

CICMS_AR_INV_LINE_INT_HIS

(

INTERFACE_LINE_ID,

INTERFACE_LINE_CONTEXT,

INTERFACE_LINE_ATTRIBUTE1,








BATCH_SOURCE_NAME,

SET_OF_BOOKS_ID,

LINE_TYPE,

DESCRIPTION,

CURRENCY_CODE,

AMOUNT,

CUST_TRX_TYPE_NAME,

CUST_TRX_TYPE_ID,

TERM_NAME,

TERM_ID,

ORIG_SYSTEM_BATCH_NAME,

ORIG_SYSTEM_BILL_CUSTOMER_REF,

ORIG_SYSTEM_BILL_CUSTOMER_ID,

ORIG_SYSTEM_BILL_ADDRESS_REF,

9

ORIG_SYSTEM_BILL_ADDRESS_ID,

ORIG_SYSTEM_BILL_CONTACT_REF,

ORIG_SYSTEM_BILL_CONTACT_ID,

ORIG_SYSTEM_SHIP_CUSTOMER_REF,

ORIG_SYSTEM_SHIP_CUSTOMER_ID,

ORIG_SYSTEM_SHIP_ADDRESS_REF,

ORIG_SYSTEM_SHIP_ADDRESS_ID,

ORIG_SYSTEM_SHIP_CONTACT_REF,

ORIG_SYSTEM_SHIP_CONTACT_ID,

ORIG_SYSTEM_SOLD_CUSTOMER_REF,

ORIG_SYSTEM_SOLD_CUSTOMER_ID,

LINK_TO_LINE_ID,

LINK_TO_LINE_CONTEXT,

LINK_TO_LINE_ATTRIBUTE1,







RECEIPT_METHOD_NAME,

RECEIPT_METHOD_ID,

CONVERSION_TYPE,

CONVERSION_DATE,

CONVERSION_RATE,

CUSTOMER_TRX_ID,

TRX_DATE,

10

GL_DATE,

DOCUMENT_NUMBER,

TRX_NUMBER,

LINE_NUMBER,

QUANTITY,

QUANTITY_ORDERED,

UNIT_SELLING_PRICE,

UNIT_STANDARD_PRICE,

PRINTING_OPTION,

INTERFACE_STATUS,

REQUEST_ID,

RELATED_BATCH_SOURCE_NAME,

RELATED_TRX_NUMBER,

RELATED_CUSTOMER_TRX_ID,

PREVIOUS_CUSTOMER_TRX_ID,

CREDIT_METHOD_FOR_ACCT_RULE,

CREDIT_METHOD_FOR_INSTALLMENTS,

REASON_CODE,

TAX_RATE,

TAX_CODE,

TAX_PRECEDENCE,

EXCEPTION_ID,

EXEMPTION_ID,

SHIP_DATE_ACTUAL,

FOB_POINT,

SHIP_VIA,

WAYBILL_NUMBER,

11

INVOICING_RULE_NAME,

INVOICING_RULE_ID,

ACCOUNTING_RULE_NAME,

ACCOUNTING_RULE_ID,

ACCOUNTING_RULE_DURATION,

RULE_START_DATE,

PRIMARY_SALESREP_NUMBER,

PRIMARY_SALESREP_ID,

SALES_ORDER,

SALES_ORDER_LINE,

SALES_ORDER_DATE,

SALES_ORDER_SOURCE,

SALES_ORDER_REVISION,

PURCHASE_ORDER,

PURCHASE_ORDER_REVISION,

PURCHASE_ORDER_DATE,

AGREEMENT_NAME,

AGREEMENT_ID,

MEMO_LINE_NAME,

MEMO_LINE_ID,

INVENTORY_ITEM_ID,

MTL_SYSTEM_ITEMS_SEG1,






12















REFERENCE_LINE_ID,

REFERENCE_LINE_CONTEXT,

REFERENCE_LINE_ATTRIBUTE1,







TERRITORY_ID,

TERRITORY_SEGMENT1,

TERRITORY_SEGMENT2,

TERRITORY_SEGMENT3,

13

TERRITORY_SEGMENT4,

TERRITORY_SEGMENT5,

TERRITORY_SEGMENT6,

TERRITORY_SEGMENT7,

TERRITORY_SEGMENT8,

TERRITORY_SEGMENT9,

TERRITORY_SEGMENT10,











ATTRIBUTE_CATEGORY,

ATTRIBUTE1,

ATTRIBUTE2,

ATTRIBUTE3,

ATTRIBUTE4,

ATTRIBUTE5,

ATTRIBUTE6,

ATTRIBUTE7,

ATTRIBUTE8,

ATTRIBUTE9,

14

ATTRIBUTE10,

ATTRIBUTE11,

ATTRIBUTE12,

ATTRIBUTE13,

ATTRIBUTE14,

ATTRIBUTE15,

HEADER_ATTRIBUTE_CATEGORY,

HEADER_ATTRIBUTE1,

HEADER_ATTRIBUTE2,

HEADER_ATTRIBUTE3,

HEADER_ATTRIBUTE4,

HEADER_ATTRIBUTE5,

HEADER_ATTRIBUTE6,

HEADER_ATTRIBUTE7,

HEADER_ATTRIBUTE8,

HEADER_ATTRIBUTE9,

HEADER_ATTRIBUTE10,

HEADER_ATTRIBUTE11,

HEADER_ATTRIBUTE12,

HEADER_ATTRIBUTE13,

HEADER_ATTRIBUTE14,

HEADER_ATTRIBUTE15,

COMMENTS,

INTERNAL_NOTES,

INITIAL_CUSTOMER_TRX_ID,

USSGL_TRANSACTION_CODE_CONTEXT,

USSGL_TRANSACTION_CODE,

15

ACCTD_AMOUNT,

CUSTOMER_BANK_ACCOUNT_ID,

CUSTOMER_BANK_ACCOUNT_NAME,

UOM_CODE,

UOM_NAME,

DOCUMENT_NUMBER_SEQUENCE_ID,






















16



VAT_TAX_ID,

REASON_CODE_MEANING,

LAST_PERIOD_TO_CREDIT,

PAYING_CUSTOMER_ID,

PAYING_SITE_USE_ID,

TAX_EXEMPT_FLAG,

TAX_EXEMPT_REASON_CODE,

TAX_EXEMPT_REASON_CODE_MEANING,

TAX_EXEMPT_NUMBER,

SALES_TAX_ID,

CREATED_BY,

CREATION_DATE,

LAST_UPDATED_BY,

LAST_UPDATE_DATE,

LAST_UPDATE_LOGIN,

LOCATION_SEGMENT_ID,

MOVEMENT_ID,

ORG_ID,

AMOUNT_INCLUDES_TAX_FLAG,

HEADER_GDF_ATTR_CATEGORY,

HEADER_GDF_ATTRIBUTE1,





17


























LINE_GDF_ATTR_CATEGORY,

LINE_GDF_ATTRIBUTE1,

18




















RESET_TRX_DATE_FLAG,

PAYMENT_SERVER_ORDER_NUM,

APPROVAL_CODE,

ADDRESS_VERIFICATION_CODE,

WAREHOUSE_ID,

TRANSLATED_DESCRIPTION,

CONS_BILLING_NUMBER,

PROMISED_COMMITMENT_AMOUNT,

19

PAYMENT_SET_ID,

ORIGINAL_GL_DATE,

CONTRACT_LINE_ID,

CONTRACT_ID,

SOURCE_DATA_KEY1,

SOURCE_DATA_KEY2,

SOURCE_DATA_KEY3,

SOURCE_DATA_KEY4,

SOURCE_DATA_KEY5,

INVOICED_LINE_ACCTG_LEVEL,

OVERRIDE_AUTO_ACCOUNTING_FLAG,

SOURCE_APPLICATION_ID,

SOURCE_EVENT_CLASS_CODE,

SOURCE_ENTITY_CODE,

SOURCE_TRX_ID,

SOURCE_TRX_LINE_ID,

SOURCE_TRX_LINE_TYPE,

SOURCE_TRX_DETAIL_TAX_LINE_ID,

HISTORICAL_FLAG,

TAX_REGIME_CODE,

TAX,

TAX_STATUS_CODE,

TAX_RATE_CODE,

TAX_JURISDICTION_CODE,

TAXABLE_AMOUNT,

TAXABLE_FLAG,

LEGAL_ENTITY_ID,

20

PARENT_LINE_ID,

DEFERRAL_EXCLUSION_FLAG,

PAYMENT_TRXN_EXTENSION_ID,

RULE_END_DATE,

PAYMENT_ATTRIBUTES,

APPLICATION_ID,

BILLING_DATE,

TRX_BUSINESS_CATEGORY,

PRODUCT_FISC_CLASSIFICATION,

PRODUCT_CATEGORY,

PRODUCT_TYPE,

LINE_INTENDED_USE,

ASSESSABLE_VALUE,

DOCUMENT_SUB_TYPE,

DEFAULT_TAXATION_COUNTRY,

USER_DEFINED_FISC_CLASS,

TAXED_UPSTREAM_FLAG,


ERROR_MESSAGE,

RECORD_NUMBER,

BATCH_ID,

SOB_NAME,

ORGNIZATION_NAME,

SET_OF_BOOKS_NAME,

DERIVED_BILL_TO_CUSTOMER_ID,

DERIVED_SHIP_TO_SITE_USE_ID,

DERIVED_SHIP_TO_CUSTOMER_ID,

21

DERIVED_BILL_TO_SITE_USE_ID,

OPERATING_UNIT_NAME,

CONCAT_SEGMENT,

CCID,

SEGMENT1,

SEGMENT2,

SEGMENT3,

SEGMENT4,

SEGMENT5,

SEGMENT6,

SEGMENT7,

SEGMENT8,

SEGMENT9,

SEGMENT10,

SEGMENT11,

SEGMENT12,

CONTRACT_EFF_DATE,

CONTRACT_TERM_DATE,

BATCH_RUN_DATE,

PRODUCT_ID,

NOL,

APPLICATION_FORM_ID

)

SELECT

(

INTERFACE_LINE_ID,

INTERFACE_LINE_CONTEXT,

22









BATCH_SOURCE_NAME,

SET_OF_BOOKS_ID,

LINE_TYPE,

DESCRIPTION,

CURRENCY_CODE,

AMOUNT,

CUST_TRX_TYPE_NAME,

CUST_TRX_TYPE_ID,

TERM_NAME,

TERM_ID,

ORIG_SYSTEM_BATCH_NAME,

ORIG_SYSTEM_BILL_CUSTOMER_REF,

ORIG_SYSTEM_BILL_CUSTOMER_ID,

ORIG_SYSTEM_BILL_ADDRESS_REF,

ORIG_SYSTEM_BILL_ADDRESS_ID,

ORIG_SYSTEM_BILL_CONTACT_REF,

ORIG_SYSTEM_BILL_CONTACT_ID,

ORIG_SYSTEM_SHIP_CUSTOMER_REF,

ORIG_SYSTEM_SHIP_CUSTOMER_ID,

23

ORIG_SYSTEM_SHIP_ADDRESS_REF,

ORIG_SYSTEM_SHIP_ADDRESS_ID,

ORIG_SYSTEM_SHIP_CONTACT_REF,

ORIG_SYSTEM_SHIP_CONTACT_ID,

ORIG_SYSTEM_SOLD_CUSTOMER_REF,

ORIG_SYSTEM_SOLD_CUSTOMER_ID,

LINK_TO_LINE_ID,

LINK_TO_LINE_CONTEXT,








RECEIPT_METHOD_NAME,

RECEIPT_METHOD_ID,

CONVERSION_TYPE,

CONVERSION_DATE,

CONVERSION_RATE,

CUSTOMER_TRX_ID,

TRX_DATE,

GL_DATE,

DOCUMENT_NUMBER,

TRX_NUMBER,

LINE_NUMBER,

QUANTITY,

24

QUANTITY_ORDERED,

UNIT_SELLING_PRICE,

UNIT_STANDARD_PRICE,

PRINTING_OPTION,

INTERFACE_STATUS,

REQUEST_ID,

RELATED_BATCH_SOURCE_NAME,

RELATED_TRX_NUMBER,

RELATED_CUSTOMER_TRX_ID,

PREVIOUS_CUSTOMER_TRX_ID,

CREDIT_METHOD_FOR_ACCT_RULE,

CREDIT_METHOD_FOR_INSTALLMENTS,

REASON_CODE,

TAX_RATE,

TAX_CODE,

TAX_PRECEDENCE,

EXCEPTION_ID,

EXEMPTION_ID,

SHIP_DATE_ACTUAL,

FOB_POINT,

SHIP_VIA,

WAYBILL_NUMBER,

INVOICING_RULE_NAME,

INVOICING_RULE_ID,

ACCOUNTING_RULE_NAME,

ACCOUNTING_RULE_ID,

ACCOUNTING_RULE_DURATION,

25

RULE_START_DATE,

PRIMARY_SALESREP_NUMBER,

PRIMARY_SALESREP_ID,

SALES_ORDER,

SALES_ORDER_LINE,

SALES_ORDER_DATE,

SALES_ORDER_SOURCE,

SALES_ORDER_REVISION,

PURCHASE_ORDER,

PURCHASE_ORDER_REVISION,

PURCHASE_ORDER_DATE,

AGREEMENT_NAME,

AGREEMENT_ID,

MEMO_LINE_NAME,

MEMO_LINE_ID,

INVENTORY_ITEM_ID,












26










REFERENCE_LINE_ID,

REFERENCE_LINE_CONTEXT,








TERRITORY_ID,

TERRITORY_SEGMENT1,

TERRITORY_SEGMENT2,

TERRITORY_SEGMENT3,

TERRITORY_SEGMENT4,

TERRITORY_SEGMENT5,

TERRITORY_SEGMENT6,

TERRITORY_SEGMENT7,

TERRITORY_SEGMENT8,

27

TERRITORY_SEGMENT9,












ATTRIBUTE_CATEGORY,

ATTRIBUTE1,

ATTRIBUTE2,

ATTRIBUTE3,

ATTRIBUTE4,

ATTRIBUTE5,

ATTRIBUTE6,

ATTRIBUTE7,

ATTRIBUTE8,

ATTRIBUTE9,

ATTRIBUTE10,

ATTRIBUTE11,

ATTRIBUTE12,

ATTRIBUTE13,

ATTRIBUTE14,

28

ATTRIBUTE15,

HEADER_ATTRIBUTE_CATEGORY,

HEADER_ATTRIBUTE1,

HEADER_ATTRIBUTE2,

HEADER_ATTRIBUTE3,

HEADER_ATTRIBUTE4,

HEADER_ATTRIBUTE5,

HEADER_ATTRIBUTE6,

HEADER_ATTRIBUTE7,

HEADER_ATTRIBUTE8,

HEADER_ATTRIBUTE9,

HEADER_ATTRIBUTE10,

HEADER_ATTRIBUTE11,

HEADER_ATTRIBUTE12,

HEADER_ATTRIBUTE13,

HEADER_ATTRIBUTE14,

HEADER_ATTRIBUTE15,

COMMENTS,

INTERNAL_NOTES,

INITIAL_CUSTOMER_TRX_ID,

USSGL_TRANSACTION_CODE_CONTEXT,

USSGL_TRANSACTION_CODE,

ACCTD_AMOUNT,

CUSTOMER_BANK_ACCOUNT_ID,

CUSTOMER_BANK_ACCOUNT_NAME,

UOM_CODE,

UOM_NAME,

29

DOCUMENT_NUMBER_SEQUENCE_ID,
























VAT_TAX_ID,

REASON_CODE_MEANING,

LAST_PERIOD_TO_CREDIT,

30

PAYING_CUSTOMER_ID,

PAYING_SITE_USE_ID,

TAX_EXEMPT_FLAG,

TAX_EXEMPT_REASON_CODE,

TAX_EXEMPT_REASON_CODE_MEANING,

TAX_EXEMPT_NUMBER,

SALES_TAX_ID,

CREATED_BY,

CREATION_DATE,

LAST_UPDATED_BY,

LAST_UPDATE_DATE,

LAST_UPDATE_LOGIN,

LOCATION_SEGMENT_ID,

MOVEMENT_ID,

ORG_ID,

AMOUNT_INCLUDES_TAX_FLAG,

HEADER_GDF_ATTR_CATEGORY,











31





















LINE_GDF_ATTR_CATEGORY,







32















RESET_TRX_DATE_FLAG,

PAYMENT_SERVER_ORDER_NUM,

APPROVAL_CODE,

ADDRESS_VERIFICATION_CODE,

WAREHOUSE_ID,

TRANSLATED_DESCRIPTION,

CONS_BILLING_NUMBER,

PROMISED_COMMITMENT_AMOUNT,

PAYMENT_SET_ID,

ORIGINAL_GL_DATE,

CONTRACT_LINE_ID,

CONTRACT_ID,

SOURCE_DATA_KEY1,

33

SOURCE_DATA_KEY2,

SOURCE_DATA_KEY3,

SOURCE_DATA_KEY4,

SOURCE_DATA_KEY5,

INVOICED_LINE_ACCTG_LEVEL,

OVERRIDE_AUTO_ACCOUNTING_FLAG,

SOURCE_APPLICATION_ID,

SOURCE_EVENT_CLASS_CODE,

SOURCE_ENTITY_CODE,

SOURCE_TRX_ID,

SOURCE_TRX_LINE_ID,

SOURCE_TRX_LINE_TYPE,

SOURCE_TRX_DETAIL_TAX_LINE_ID,

HISTORICAL_FLAG,

TAX_REGIME_CODE,

TAX,

TAX_STATUS_CODE,

TAX_RATE_CODE,

TAX_JURISDICTION_CODE,

TAXABLE_AMOUNT,

TAXABLE_FLAG,

LEGAL_ENTITY_ID,

PARENT_LINE_ID,

DEFERRAL_EXCLUSION_FLAG,

PAYMENT_TRXN_EXTENSION_ID,

RULE_END_DATE,

PAYMENT_ATTRIBUTES,

34

APPLICATION_ID,

BILLING_DATE,

TRX_BUSINESS_CATEGORY,

PRODUCT_FISC_CLASSIFICATION,

PRODUCT_CATEGORY,

PRODUCT_TYPE,

LINE_INTENDED_USE,

ASSESSABLE_VALUE,

DOCUMENT_SUB_TYPE,

DEFAULT_TAXATION_COUNTRY,

USER_DEFINED_FISC_CLASS,

TAXED_UPSTREAM_FLAG,


ERROR_MESSAGE,

RECORD_NUMBER,

BATCH_ID,

SOB_NAME,

ORGNIZATION_NAME,

SET_OF_BOOKS_NAME,

DERIVED_BILL_TO_CUSTOMER_ID,

DERIVED_SHIP_TO_SITE_USE_ID,

DERIVED_SHIP_TO_CUSTOMER_ID,

DERIVED_BILL_TO_SITE_USE_ID,

OPERATING_UNIT_NAME,

CONCAT_SEGMENT,

CCID,

SEGMENT1,

35

SEGMENT2,

SEGMENT3,

SEGMENT4,

SEGMENT5,

SEGMENT6,

SEGMENT7,

SEGMENT8,

SEGMENT9,

SEGMENT10,

SEGMENT11,

SEGMENT12,

CONTRACT_EFF_DATE,

CONTRACT_TERM_DATE,

BATCH_RUN_DATE,

PRODUCT_ID,

NOL,

APPLICATION_FORM_ID

)

FROM apps.xx_ar_inv_line_pre_int_his

WHERE BATCH_RUN_DATE BETWEEN '17-OCT-2012' AND '25-OCT-2012';

Query to update Hold and Release Flag in Commission Table

--------------- Hold --------------------

update cicms_commission_calc_info

set attribute10='Y',hold_type='MANUAL'

36

,hold_reason='Hold Applied during 16-30 Sep Commission Pay Cycle',

hold_applied_by=1111,hold_applied_date=sysdate

where trunc(batch_run_date) = '29-OCT-2012'

and attribute10 ='N'

and policy_number in

(

'30133854201200'

);

--------------- Release --------------------

update cicms_commission_calc_info

set attribute10='N'

,RELEASE_REASON='Manually Released during 1-15 Sep Commission Pay Cycle',

RELEASED_BY=1111,RELEASE_DATE=sysdate

where trunc(batch_run_date) = '24-SEP-2012' and

attribute10 = 'Y'

and policy_number in

(

'30133854201200'

);

1. Attribute1=Y (Approved) and Attribute1=N (Not Approved)2. Attribute10=Y (Hold) and Attribute10=N (Release).3. After Hold and Release of Appropriate Policies,4. Query Commission Workbench with BATCH_RUN_DATE and

Approve for the same.

37

5. Run Transferred to AP Report and match the sum from CICMS_COMMISSION_CALC_INFO

6. Run the CICMS Commission Data To AP Invoice Interface Program (Mail) with GL_DATE and DESCRIPTION.

38

steps for reverse-commission

Documents